[jhobbs]

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.

[Pat Phelan]

Are you looking for Sum() and GROUP BY ?

-PatP

[jhobbs]

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.

[Pat Phelan]

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

[jhobbs]

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.

[Pat Phelan]

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

[jhobbs]

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

This is frustrating.

[Pat Phelan]

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

-PatP

[jhobbs]

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

Jeff