Problem with differing values creating multiple lines

[Uvuriel03]

I have a query that, for the most part, sums the data for any given Job Number.

My problem is that 9 out of 10 of the job numbers are the same--9 pallets all with the exact same parts with the exact same quantities... And then the 10th is an oddball.

So when I run my query, I get a summary of the 9 pallets, and then a single line of the oddball pallet.

For example:
I have 6 pallets of "Part 1", 5 of which have 40 boxes on the pallet. Each box has 10 "Part 1"s inside. So there is a total of 200 boxes of "Part 1", with a total quantity of 2,000 units.

The 6th box has 23 boxes on the pallet. We don't want to count any partial pallets as a whole one--rather just add the quantity inside to the total count. So we have 22 boxes with 10 inside, and a 23rd with only 4 inside.

So when I run the query, it looks like this (very simplified):

Part____|Boxes__|Cases_|Partials_|Total
Part 1__|200____|10____|0______|2000 (200*10 + 0 This doesn't actually need to show up, it's just to clarify the total count)
Part 1__|22_____|10____|4______|224 (22*10 + 4)

I would rather it add those two lines together so it looks like this:

Part____|Boxes__|Cases_|Partials_|Total
Part 1__|222____|10____|4______|2224 (222*10 + 4)

But I'm not sure if that's even possible to do in a query...

And the reason I'm not doing this in a report is because it needs to be exported to excel, and you can only export reports and tables in Access 2007. Wheeee, fun.

 

[fredalina]

Do you have any Sum fields? You can add the Sum line and only group by like things, like part number, and sum everything else.

 

[Uvuriel03]

Yes, I have it summed by the Job Number, but that only makes it sum the pallets that have the exact same quantities.

 

[fredalina]

What is the Job Number?

You should group by Part Number (and also Job Number if applicable), but Sum everything else. Is that what you're already doing?

 

[Uvuriel03]

I figured it out with a little more tweaking. Thanks!