Combine multiple records and add amount

[Guest]

I may have asked this question last week, but I can't find my post and I
don't have anything in my notes, which I normally take, so I apologize if
this is a second posting.

I need to create a query to combine multiple records in a table that have
the same SSN and Name, but different Amounts, into one record. E.g., the 3
records: 123456789 John Doe $125, 123456789 John Doe $50, 123456789 John Doe
$50 should combine into 123456789 John Doe $225; and the 2 records: 111222333
Jane Anybody $100, 111222333 Jane Anybody $30 should combine into 111222333
Jane Anybody $130; etc.

Thanks in advance,

 

[Carl Rapson]

I may have asked this question last week, but I can't find my post and I
don't have anything in my notes, which I normally take, so I apologize if
this is a second posting.

I need to create a query to combine multiple records in a table that have
the same SSN and Name, but different Amounts, into one record. E.g., the
3
records: 123456789 John Doe $125, 123456789 John Doe $50, 123456789 John
Doe
$50 should combine into 123456789 John Doe $225; and the 2 records:
111222333
Jane Anybody $100, 111222333 Jane Anybody $30 should combine into
111222333
Jane Anybody $130; etc.

Thanks in advance,

How about:

SELECT SSN, Name, Sum(Amount)
FROM table
GROUP BY SSN, Name;

Carl Rapson

 

[Guest]

Thanks for the response. However, that didn't work. I have multiple records
of the same person but with different amounts; I need to end up with only one
record per person, but with the sum total of the amounts for each person.
Hope that makes sense. Thanks for your help.

 

[Carl Rapson]

Thanks for the response. However, that didn't work. I have multiple
records
of the same person but with different amounts; I need to end up with only
one
record per person, but with the sum total of the amounts for each person.
Hope that makes sense. Thanks for your help.

What do you mean by "that didn't work"? The GROUP clause should consolidate
all records with the same SSN and Name into a single record, summing the
amounts. What did you see when you tried the code I gave?

Carl Rapson

 

[Guest]

I'm sorry, I should have been more explicit. I went back and tried your code
again, this time only using the SSN, Name, and Amount fields, and it worked
fine. I only stated those fields to make my question easier; I actually have
about 15 fields that I need to be displayed as well, but the important ones
are the SSN, Name, and Amount. When I display all the fields, then the code
doesn't work; it displays all the records rather than combining them. I'm
summing the Amount field, as you indicated, and grouping all the other
fields, as you indicated for the SSN and Name. Is there a limit to the
fields that can be displayed, or something I'm missing?

Thanks for your help. I appreciate it.

 

[John W. Vinson]

I'm sorry, I should have been more explicit. I went back and tried your code
again, this time only using the SSN, Name, and Amount fields, and it worked
fine. I only stated those fields to make my question easier; I actually have
about 15 fields that I need to be displayed as well, but the important ones
are the SSN, Name, and Amount. When I display all the fields, then the code
doesn't work; it displays all the records rather than combining them. I'm
summing the Amount field, as you indicated, and grouping all the other
fields, as you indicated for the SSN and Name. Is there a limit to the
fields that can be displayed, or something I'm missing?

You can display the fifteen fields for the individual records... or you can
display the sum... but you can't (at all easily) display both in the same
record. If you are summing 231 records, do you want to see the same sum 231
times??? What do you WANT to see? Might it not be better to use a Form or
Report, display the individual records in the Detail section, and display the
sum on a Footer?

John W. Vinson [MVP]

 

[Carl Rapson]

One alternative would be to create a query based on the code I gave you,
then use that query as the basis for another query that also links in the
table where the remaining fields reside. Something like this (assuming the
name of the table is 'table'):

Query1:
SELECT SSN, Name, Sum(Amount) AS [MySum]
FROM table
GROUP BY SSN, Name;

Query2:
SELECT SSN, Name, MySum,field1,field2,field3,...
FROM Query1 INNER JOIN table
ON Query1.SSN=table.SSN AND Query1.Name=table1.Name;

Carl Rapson