combine or total a result

D

Denise

table info:
Alaska $1,200 Product A
Alaska 300 Product B
Alabama 900 Product A
Alabama 100 Product B

Query objective: sum product dollars by state.

How do I combine Alaska results to show one sum for both products (result
1,500)?

Or what expression would I use to sum column 2 where the states are equal
(result for Alaska $1,500, Alabama 1,000, etc.)?
 
K

KARL DEWEY

Try this --
SELECT YourTable.State, Sum(YourTable.Amount) AS SumOfAmount
FROM YourTable
GROUP BY YourTable.State;
 
J

John W. Vinson

table info:
Alaska $1,200 Product A
Alaska 300 Product B
Alabama 900 Product A
Alabama 100 Product B

Query objective: sum product dollars by state.

How do I combine Alaska results to show one sum for both products (result
1,500)?

Or what expression would I use to sum column 2 where the states are equal
(result for Alaska $1,500, Alabama 1,000, etc.)?
Click to expand...

You need a Totals query.

Create a Query based on the table; add the fields for state and amount (you'll
need to leave out the product).

Change the query to a Totals query by clicking the Greek Sigma icon (looks
like a sideways M). Leave the default Group By on the state name field, and
change it to Sum on the amount.

John W. Vinson [MVP]
 
D

Denise

Here's a bit more info: there are multiple products/amounts in one table and
I want to choose A and B only. I could just create multiple tables by
product and that would solve the problem. Could there be some sort of
logical expression I could use?
 
K

KARL DEWEY

Try this --
SELECT YourTable.State, Sum(YourTable.Amount) AS SumOfAmount
FROM YourTable
WHERE Table.Product = "A" Or Table.Product = "B"
GROUP BY YourTable.State;
 
D

Denise

It worked. Thanks so much.

KARL DEWEY said:
Try this --
SELECT YourTable.State, Sum(YourTable.Amount) AS SumOfAmount
FROM YourTable
WHERE Table.Product = "A" Or Table.Product = "B"
GROUP BY YourTable.State;
Click to expand...
 
Top