Can I combine related records

[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?

 

[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>

 

[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

 

[ZBC]

Kevin,
You solved my problem ... I had "Group By" in other fields!
Thanks a Bunch!
Bob