Multiplying two values from the same column

[jplank]

Names Number1 Number2
Name1 1 2
Name1 2 1
Name1 3 -2
Name1 4 2
Name1 5 5
Name2 1 2
Name2 2 5
Name2 3 2
Name2 4 5
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).

 

[plog]

This should do it:

SELECT YourTableNameHere.Names, Min([Number2])*Max([Number2]) AS First2Product
FROM YourTableNameHere
WHERE (((YourTableNameHere.Number1)=1 Or (YourTableNameHere.Number1)=2))
GROUP BY YourTableNameHere.Names;

Replace all instances of 'YourTableNameHere' with the name of your table. Here's how it works: I built a query to show just the records where [Number1] was 1 or 2. That got me to 2 records per [Names] field. Since you want the product of just 2 numbers, I figured I could take the maxium of those 2 values and multiply it by the minimum of those two values and get you what you wanted. Now, if you want to multiply the first 3 values, then you get a little more complicated and the system has to be rethought. But the query above will work for what you want.