Grouping question

[Uvuriel03]

I've got a report I'm working on that needs to take a list of items between two dates and determine whether it should be counted per case or by pallet.

Basically, if there are more than 12 cases, it is counted as a pallet. If not, they are counted by case.

I need to figure out how to sort and count these accordingly--For anything less than 12 cases, I need to count each case, and for anything more than or equal to 12 cases, I need to count it as one pallet.

I also need the report to group it accordingly--anything less than 12 cases under "Cases", and anything more than or equal to 12 under "Pallets".

Any ideas?
Thanks!

 

[namliam]

How about you take all your cases and devide by 12, then sum that:
Sum(Case / 12)

This will probably get you an answer like
4.25 = 4 pallets and 3 Cases

Or is that not what you are looking for ?

 

[Uvuriel03]

Thanks, but that's not quite what I need.

I need to determine whether each receipt we get is a full pallet of items or just a few boxes of it, depending on whether the count was more than or less than 12 boxes.

Then I need my report to sort them by pallets vs cases.

 

[namliam]

And what would 13 cases be? 1 pallet and 1 case?

Try putting this in a query, order by the order field...
OrderField Iif(Case > 12;1;2)
Pallets: Iif(Case > 12; case;0)
Cases: Iif(Case <= 12; case;0)

This might be it?? The order field makes sure that any pallets come on top of your query, while pallets come at the bottom.