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!