How to COMBINE data in rows using query?

[ciel220]

Hello,

Would anyone able to give me some hints on how to combine data across multiple rows using query?

Here is what on my table:

Person Group_ID Sales
------ -------- -----
A1 00 2
B 01 3
C 02 1
A2 00 3
B 01 1
A3 00 2
(Although persons name differs, their group IDs are same)

Here is what I want (after running a query):

Person Group ID Aggreg_Sales
------ -------- ------------
A1 00 7
B 01 4
C 02 1
(Name of person selected by whichever shows up first)


I'm so frustrating now and I couldn't figure out what to do =( Please give me some help!

Thanks a lot! THANKS!

ciel220

 

[cmmrfrds]

select Min(Person) as person, Group_ID, Sum(Sales) as sumsales
From mytable
group by Group_ID

 

[AutumnBlues]

Paste this into the SQL window and then flip back to QBE(you'll first need to change the table name to fit)

SELECT First(Person) AS Person99, GroupID, Sum(Sales) AS SumOfSales
FROM Table1
GROUP BY GroupID;

 

[AutumnBlues]

SNAP! (ish)

 

[JeremyNYC]

Ciel,

What you want to do is a totals query, where you group by the first two fields you've shown and sum on the last one.

If you look in the help file for totals query you'll find what you need, but basically you want to create a new query (without a wizard), add those three fields, click on the sigma (greek E), and set the last field to Sum, where they all say Group By.

Hope this helps.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing and building Access databases since 1995.

 

[ciel220]

All of the reply are helpful!
Thanks a bunch!!!!

ciel