How to divide/distribute books to boxes

[masoud_sedighy]

I have a list that shows how many books we have for each PO No. (Sorted by PO No.) Like below:
LIST 1:

PO No		TOTAL BOOKS
PO-0001	            12
PO-0002	            15
PO-0003	            26

Now I would like to distribute/divide these different books to boxes and each box gets maximum 10 books. Like below list:

LIST 2:

BOX NO		BOOKS		PO NO
0001		    BOOK 1~10	     PO-0001
0002		    BOOK 11~12	     PO-0001
0002		    BOOK 1~8	     PO-0002
0003		   BOOK 9~15	     PO-0002
0003		   BOOK 1~3 	     PO-0003
0004		   BOOK 4~13	    PO-0003
0005		   BOOK 14~23	    PO-0003
……

format is not important in the list2, for example in Box 0002 we have book 11~12 means (book 11 of 12 +book 12 of 12) in po-0001

+ book 1 of 15 until book 8 of 15) in po-0002

books have bookno, for example for po-0001 we have 12 different books it means we have bookno (1 of 12) until (12 of 12) for this po no.



Please help how I can produce automatically list 2 from list 1 by query or coding.

 

[Isskint]

hi masoud_sedighy

I can see a way to achieve this with coding but not queries - although i am sure it can be done with queries.

From a code viewpoint you would need to loop through each PO in the table,adding each book one at a time to a box.

• Loop through PO table
• Loop through contents of PO
• Add (use an append SQL) each book to current box
• increment counter and check not > max box contents (10?) OR DCOUNT() current box
• Start new box if >10 OR continue with current box
• Loop
• Loop

I will watch this thread with interest, as this feature is very similar to something i currently do.

 

[jdraw]

Here is a link to thread in which a poster was trying to put items into boxes. It is different than your book description, but the dialog (story in the posts) may be useful to you.
Good luck with your project.