combine or total a result

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.)?
 
Try this --
SELECT YourTable.State, Sum(YourTable.Amount) AS SumOfAmount
FROM YourTable
GROUP BY YourTable.State;
 
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]
 
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?
 
Try this --
SELECT YourTable.State, Sum(YourTable.Amount) AS SumOfAmount
FROM YourTable
WHERE Table.Product = "A" Or Table.Product = "B"
GROUP BY YourTable.State;
 
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...
 
Back
Top