Access Sumproduct

Question

I have an Excel workbook that has a sumproduct worksheet based on the information in another worksheet (example below).  It looks at the Name column and tells me how many B's or NB's Joe Smith has for Question 1 etc..  It's huge and I've been asked to put it into Access 2010.  I've been researching this, but can't find a way to make the same thing happen in Access.  Any suggestions, please?

Name	Question1	Question2
Joe Smith	NB	S
Joe Smith	B	B
Joe Smith	B	B
Mary Jones	B	NB
Mary Jones	B	NB
Mary Jones	B	B

Answer 1

You should not use Name as a field in Access as it is a reserved word and may cause problems. 

Use a union query to get to proper table structure --

SELECT Name AS Student, 1 AS Question, Question1 AS Score

FROM YourExcelInput

WHERE Question1 Is Not Null

UNION ALL

SELECT Name AS Student, 2 AS Question, Question2 AS Score

FROM YourExcelInput

WHERE Question2 Is Not Null

SELECT Name AS Student, 3 AS Question, Question3 AS Score

FROM YourExcelInput

WHERE Question3 Is Not Null

etc;

Answer 2

Thank you both for the suggestion.

Answer 3

In Access, you should use a different table structure:

Name         Question  Score

Joe Smith          1       NB

Joe Smith          1       B

Joe Smith          1       B

Joe Smith          2       S

Joe Smith          2       B

Joe Smith          2       B

Mary Jones       1       B

...

You can then use Totals queries and Crosstab queries (a special form of a Totals query) to tally the numbers grouped by name and by question.