Query totaling one field for unique values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cathy C - 16 Apr 2008 17:10 GMT

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

Reply to this Message

KARL DEWEY - 16 Apr 2008 18:05 GMT

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.

Signature

KARL DEWEY
Build a little - Test a little

> 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
[quoted text clipped - 7 lines]
>
> Cathy

Reply to this Message

Cathy C - 16 Apr 2008 18:23 GMT

Thanks so much! That did it.

> Open up a new query in design view, click on menu VIEW - SQL view and
> paste
[quoted text clipped - 19 lines]
>>
>> Cathy

Reply to this Message