INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Using Functions in Queries

Summing fields, and Collapsing them together (GROUP BY) by NightZEN
Posted: 30 Apr 04

I recently needed to collapse records together and total some fields and descovered how easy it is to do.  I hope this might help someone eles as it seemed like a complex problem to me at the time, but turned out to have a very simple solution that SQL is perfect for handling:

Let's say you have these fields in a table or query that list sales predictions by salesman name, Customer, and Job number with dollar values in each quarter of a year, but each record contains only one value in either Q1, Q2, Q3, or Q4 and you need to view them togther:

Key
SalesName
Customer
Job
Q1
Q2
Q3
Q4

1st:  Create a new query with all of the fields accept Key

2nd:  Add the Totals Line by selecting VIEW/TOTALS

3rd:  Leave GROUP BY selected for SalesName, Customer, and Job

4th:  Select SUM for Q1, Q2, Q3, and Q4 in the GROUP BY line

Thats it!  The results will group the records into single rows when SalesName, Customer, and Job all match, and total Q1, Q2, Q3, and Q4 individualy for each row.  Beautiful!

Back to Microsoft: Access Queries and JET SQL FAQ Index
Back to Microsoft: Access Queries and JET SQL Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close