Grouping by certain qty.

[reldridge]

So i have this project that has been being done manually that I am wondering ways we could automate this.
We have locations that we need to box a certain a qty of packages and ship. Based on the location we can put up to 4000 units to a box. I need to create a report that will show what is in each box up to the qty4000.



Looking at the data below their would be 4 boxes, but i need to show what is in each box on the lable. Basically it would just say box 1 of 4, and then list out each line item in the box...

Raw data for the project looks as below:

IN_CARE_OF QUANTITY LOCATION CARD
AIRPORT STATION 200 RACK AIRPORT STATION 1
AIRPORT STATION 200 RACK AIRPORT STATION 4
AIRPORT STATION 2000 RACK AIRPORT STATION 005/10
AIRPORT STATION 200 RACK AIRPORT STATION 7
AIRPORT STATION 1000 RACK AIRPORT STATION 8
AIRPORT STATION 800 RACK AIRPORT STATION 9
AIRPORT STATION 200 RACK AIRPORT STATION 11
AIRPORT STATION 6000 RACK AIRPORT STATION 14

I think the first step is to alter the records so no line item is over 4000 to be able to figure a way to group these up too 4000 in qty.
If [QTY] > 4000 I need to duplicate the line item and adjust the quatity. So looking at the last item would change to:

AIRPORT STATION 4000 RACK AIRPORT STATION 14
AIRPORT STATION 2000 RACK AIRPORT STATION 14

Can this be done with a macro in Access or maybe even Excel?

Then I have been running test trying to figure out how to also group these by 4000, maybe using a percentage colume (Qty / 4000) and then a sum of all percentages divided by 4000 to get the # of boxes?

Thanks
Ryan.

[June7]

This is not an easy query to build.

Review
http://www.sqlmag.com/article/tsql3/...product-totals
http://stackoverflow.com/questions/7...t-reaches-1000