SQL Summation

[Jayson]

I have made an access query which produces a series of fields, 2 of those fields are : 'SessionCounter' and 'TotalAmount'

What i need to do, is develop another query or modify this existing one so that, I could Sum all the 'TotalAmount', which has the same 'SessionCounter'.

Ex.

SessionCounter TotalAmount
1234 $5000
1234 $6000
2342 $7000
1234 $1000

So I need to add $5000 + $6000 + $1000 (they all have the same SessionCounter)
Note: they are both in the same resulting table.

How do i go about doing this? thanks!

 

[ghubbell]

Hi Jason,
Place the query in design view and hit the "Totals" (sigma symbol) button on the toolbar. You just gained a row that says totals. in your TotalAmount Column, switch "Group by" to "Sum" and see if that will do it for you ;-) Gord
ghubbell@total.net

 

[Jayson]

Thanks I did try that before, but i guess my problems comes when I try to display the other fields, what do i choose for the "Total" catergory for the other fields.

This is what i did:

Field : TotalAmount SessionCounter Lastname Firstname
Table : table1 table1 table1 table1
Total : Sum Group By ???? ????

It keeps giving me an error whenever i choose other options for lastname and firstname.

Thanks for your patience

 

[ghubbell]

Totals Queries are an odd breed. Anything else you add should be pretty much "Group by". What happens though is if there are many possible "firstnames" you'll end up with odd results.

A neat trick is to let a report wizard build you a report based off of the original query. The report wizard asks all the right questions regarding grouping and summing. Take a look at the report and if you like the results, put it in to design view and look at its "Record Source". It's the query you need. You can click the "..." button beside to open it, then save it as whatever you want. Hope that helps you! Gord
ghubbell@total.net