Multiplying two values from the same column

[jplank]

Name	Number1	Number2
Name1	1	2
Name1	2	1
Name1	3	-1
Name1	4	3
Name1	5	5
Name2	1	2
Name2	2	5
Name2	3	2
Name2	4	0
Name2	5	1

...
...

For each Name, Number1 will always contain the values from 1 to 5. Number2 can vary, however.

I need help constructing a query that, for each name, will look at the values in the Number2 column when Number1 is 1 or 2 and multiply them. So, the result I'm looking for is:

Name1	2
Name2	10

(Since 2 = 2 x 1 and 10 = 2 x 5)



I haven't had much look Googling (most search results involve multiplying two columns together, which isn't exactly what I'm after).

[lfpm062010]

This query will work as long as there is only one value per [Name] and [Number1]. If there are more than one value per [Number1] = 1 or 2 then this query will not work.

SELECT DISTINCT Test1.Name1, DLookUp("Number2","Test1","Name1 = """ & [Name1] & """" & " AND Number1 = 1")*DLookUp("Number2","Test1","Name1 = """ & [Name1] & """" & " AND Number1 = 2") AS Expr3
FROM Test1
WHERE (((Test1.Number1)=1 Or (Test1.Number1)=2));