Can I combine related records

Z

ZBC

I have a query that selects the records and fields of interest.
Now I want to combine all records that relate to a particular field
(CaseNumber) by summing some of the other fields in the existing query.
i.e. there may be 1,2, or more records that refer to each CaseNumber ...
I want 'CaseNumber' to appear only once in my query (with some of the
other fields like Expenses, Travel, etc. summed.
Is there an easy way to do this?
 
J

Jeff Boyce

Have you looked into a "Totals" query? From your description, it sounds
like a possibility. Open the query designer window and add your table (and
fields). Find the toolbar button that looks like a Greek 'sigma'.

Good luck

Jeff Boyce
<Access MVP>
 
K

Kevin Sprinkel

Yes, as Jeff Boyce said, use a Totals query (View, Totals
from Query Design View). In the Total row, choose "Group
By" for the CaseNumber field, and "Sum" for the fields on
which you wish to calculate a subtotal for each case
number. Do not "Group By" any other fields, or Access
will calculate a subtotal for each unique combination of
those fields. Delete any unneeded fields and save the
totals query as a new query.

HTH
Kevin Sprinkel
 
Top