Query totaling one field for unique values

  • Thread starter Thread starter Cathy C
  • Start date Start date
C

Cathy C

I have a query with SSN, FNAME, LNAME, and AMT. Each SSN has at least one
record; several have up to four different AMTs. I need to do a Make Table
query which will include one record for each SSN and the total AMT. I also
need their FNAME and LNAME. I can do a query that gives me SSN and the total
AMT, but I can't figure out how to get the FNAME and LNAME for each of
those. I tried to do a join query, but it gives me duplicate records and the
total of all the AMTs does not equal the total in the original table.

Thanks for your assistance.

Cathy
 
Open up a new query in design view, click on menu VIEW - SQL view and paste
this in the window --
SELECT CathyC.SSN, CathyC.FNAME, CathyC.LNAME, Sum(CathyC.AMT) AS SumOfAMT
FROM CathyC
GROUP BY CathyC.SSN, CathyC.FNAME, CathyC.LNAME;

Edit it replacing CathyC with your table name.
 
Thanks so much! That did it.
KARL DEWEY said:
Open up a new query in design view, click on menu VIEW - SQL view and
paste
this in the window --
SELECT CathyC.SSN, CathyC.FNAME, CathyC.LNAME, Sum(CathyC.AMT) AS SumOfAMT
FROM CathyC
GROUP BY CathyC.SSN, CathyC.FNAME, CathyC.LNAME;

Edit it replacing CathyC with your table name.
Click to expand...
 
Back
Top