A2K - How to merge rows in a table

#1

I have some rows with duplicate fields and numerical values in other fields.
My source is a select query.

e.g

Mr Sad|150.00|20
Mr Sad|23.56|12
Mr Happy|90.3|34

I want to end up with

Mr Sad|173.56|22
Mr Happy|90.3|34

I've tried to nest the query into another query but I'm ending up with the
same number of rows and they aren't merging.

thanks

#2

"Ed J." <ed (AT) smbpartners (DOT) com> wrote

Quote:

You need an aggregate query:

SELECT Sum(Field2), Sum(Field3)
FROM yourtable
GROUP BY Field1

In the query designer, click on the Summation key. All fields will
default to the GROUP BY clause. Change it to the SUM function for
your numeric fields. That will create the SQL you are looking for.
You don't need any nesting.

Thanks I should have added that the select query is already set up as an
aggregate. Problem is I've got various other fields using Group By and I
can't change those to SUM.

#3

buckskin wrote:

Quote:

"Ed J." <ed (AT) smbpartners (DOT) com> wrote in message
news:939e1fca-0bb2-4c02-94c4-

You need an aggregate query:

SELECT Sum(Field2), Sum(Field3)
FROM yourtable
GROUP BY Field1

In the query designer, click on the Summation key. All fields will
default to the GROUP BY clause. Change it to the SUM function for
your numeric fields. That will create the SQL you are looking for.
You don't need any nesting.

Thanks I should have added that the select query is already set up as
an aggregate. Problem is I've got various other fields using Group
By and I can't change those to SUM.

You're going to have to show us some sample data that truly illustrates your
problem. Cut it down to the minimum needed to show your problem. Obviously
you did a little too much cutting in your first attempt.

#4

"Bob Barrows" <reb01501 (AT) NOSPAMyahoo (DOT) com> wrote

Quote:

buckskin wrote:
"Ed J." <ed (AT) smbpartners (DOT) com> wrote in message
news:939e1fca-0bb2-4c02-94c4-

You need an aggregate query:

SELECT Sum(Field2), Sum(Field3)
FROM yourtable
GROUP BY Field1

In the query designer, click on the Summation key. All fields will
default to the GROUP BY clause. Change it to the SUM function for
your numeric fields. That will create the SQL you are looking for.
You don't need any nesting.

Thanks I should have added that the select query is already set up as
an aggregate. Problem is I've got various other fields using Group
By and I can't change those to SUM.

You're going to have to show us some sample data that truly illustrates
your
problem. Cut it down to the minimum needed to show your problem. Obviously
you did a little too much cutting in your first attempt.

Indeed, sorry for the late reply and I would like to wind this up by saying
I found a solution. One text field was acting as a flag denoting different
types of expenses. I changed this to First from Group By and now it works
nicely.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

A2K - How to merge rows in a table

comp.databases.ms-access comp.databases.ms-access

A2K - How to merge rows in a table - 09-20-2011 , 08:52 PM

Re: A2K - How to merge rows in a table - 09-21-2011 , 02:56 AM

Re: A2K - How to merge rows in a table - 09-21-2011 , 05:32 AM

Re: A2K - How to merge rows in a table - 09-26-2011 , 03:34 PM