I'm working on project that deals with upc numbers along with a quantity sold and retail numbers and such. My problem is that I have the same upc number on multiple times, I need these upc numbers to only appear once and have the other corresponding data to add up and appear on that same line.

Here is the code I am using:

PARAMETERS [Beginning Date] DateTime, [Ending Date] DateTime;
SELECT HIDATA60404.UPC, HIDATA60404.LONG_NAME, HIDATA60404.INV_SDESC, HIDATA60404.DEPARTMENT, HIDATA60404.CATEGORY, HIDATA60404.QSOLD, HIDATA60404.DSOLD, HIDATA60404.TOTALCOST, HIDATA60404.PROFIT, HIDATA60404.GPROFIT
FROM HIDATA60404
WHERE (((HIDATA60404.ELSDATE) Between [Beginning Date] And [Ending Date]) AND (([HIDATA60404]![ELSSTORE]) Like [store]) AND (([HIDATA60404]![CATEGORY])=[cat])) OR ((([store]) Is Null));

Thanks for any help you can provide.

Are you looking for Sum() and GROUP BY ?

-PatP

Honestly, I'm not sure where to go from here, It's been about 4-5 years since I've done any of this and I'm just trying to remember what I've done in the past. I thought it was somewhere along the lines of Select Distinct, but I have no clue if that's right.

Can you post a sample that shows around a dozen rows of data, and the corresponding result set that you'd like to see?

-PatP

Using the following code, I get 40 rows returned. Combining the results should make 17.

SELECT HIDATA60404.UPC, HIDATA60404.LONG_NAME, HIDATA60404.INV_SDESC, HIDATA60404.DEPARTMENT, HIDATA60404.CATEGORY, HIDATA60404.QSOLD, HIDATA60404.DSOLD, HIDATA60404.TOTALCOST, HIDATA60404.PROFIT, HIDATA60404.GPROFIT
FROM HIDATA60404
WHERE ((([HIDATA60404]![ELSSTORE])="10" And ([HIDATA60404]![ELSSTORE])="10") AND (([HIDATA60404]![CATEGORY])="141" And ([HIDATA60404]![CATEGORY])="141") AND ([HIDATA60404]![ELSDATE]>#5/23/2004# And [HIDATA60404]![ELSDATE]<#5/27/2004#));

I need the corresponding columns to total up with the single row i post.

I can't find the results that you wanted to see, so this is just a guess, but I'm reasonably sure that you want to group by the first column, and Sum() the numeric columns.

-PatP

I tried that. It groups the items together, but still shows up on multiple lines.

This is frustrating.

If all you want is the "galactic total", just leave off the GROUP BY.

-PatP

I did end up getting this to work. I had been trying that all day, Thanks for all your help.

Jeff