SQL Question

[JeffK627]

Hello All,

Can someone help me out with a SQL query in Access? I need to turn this result set:

Product		Business_Unit	Code		State	State_Category		Quarter		TypeA_Units	TypeB_Units	TypeC_Units
PRODUCTA	Maintenance	00071053223	CA	CategoryA		Q1_2008		0		0		122.76
PRODUCTA	Maintenance	00071053223	CA	CategoryA		Q1_2008		0		2220.46		0
PRODUCTA	Maintenance	00071053223	CA	CategoryA		Q1_2008		1330		0		0

Into this:

Product		Business_Unit	Code		State	State_Category		Quarter		TypeA_Units	TypeB_Units	TypeC_Units
PRODUCTA	Maintenance	00071053223	CA	CategoryA		Q1_2008		1330		2220.46		122.76

In other words, I need to consolidate the 3 rows into 1 row with all the non-zero values for the 3 types of Units, as long as the first 6 fields are the same for all 3 rows. Any help will be much appreciated!

 

[Joe4]

Take a look at Aggregate (Totals) Queries in Access help.

Basically, you will click on the Totals button in the Query Builder, which adds a Totals Row with the phrase "Group By" under each field. Just change the last three numeric fields from "Group By" to "Sum".

 

[JeffK627]

Thanks Joe, that worked!