Help on Consolidating data

[Guest]

Is there a way to consolidate data for one particular item (record), without
converting into a Pivot Table? Example: If I have 60 units for the year 2005
and 50 units for the year 2006 and I wanted to have them combined into the
same product number (1111), the moment I run the query? The catch is that I
need the rest of the columns that are creating the duplicatation, so I keep
getting:

1111..2005...60 units
1111..2006...50 units

and I want

1111...110 units and if I leave out the year column, I'll get all of the
previous years included...2004, 2003,etc. and I don't

 

[John Vinson]

Is there a way to consolidate data for one particular item (record), without
converting into a Pivot Table? Example: If I have 60 units for the year 2005
and 50 units for the year 2006 and I wanted to have them combined into the
same product number (1111), the moment I run the query? The catch is that I
need the rest of the columns that are creating the duplicatation, so I keep
getting:

1111..2005...60 units
1111..2006...50 units

and I want

1111...110 units and if I leave out the year column, I'll get all of the
previous years included...2004, 2003,etc. and I don't

Use a Totals query, and change the aggregate function on the year
column to "Where" instead of "Group By"... or, if you want all years,
leave it out altogether.

John W. Vinson[MVP]