Append query to receive distributed result (1 Viewer)

I am developing a database for a warehouse, I want to make an append query that keep repeating itself until a certain condition is met.

For example, I receive bulk quantity shipment in our warehouse and then we palletize them according to the packing type. I want to have following transactions
S.No Item Code Quantity Rcvd
1 A112550 145 cases


suppose A pallet is made with 40 cases, than upon executing of query, I want to receive result on subform as below;
s.no Item code Qty pallet
1. A112550 40 1
2. A112550 40 1
3. A112550 40 1
4. A112550 25 1


Hope I have explained my query requirement clear. If append query is not workable then VB code will also be welcome.

Good morning azeeshan,

It is difficult to answer this question directly without knowing a little more about how your tables are set up/structured.

Your output could be achieved with a simple equation or a For/Each Loop depending on the type of display you desire. You mention a sub-form but I think maybe you meant a sub-report as part of a main form?

At any rate, before you go any further, the underlying constant is sound tables. Can you provide some info on those?

Dear friend,

let me simplify, suppose i want distribute 15 cookies into the children of GroupA and any child can be given max 6 cookies. Thus upon distribution 2 children will have 6,6 cookies and one will have 3. thus no matter what quantities of cookies i have, it should be automatically distributed one by one 6 each and last one get rest.

I have 3 tables. One table make formA and rest of the tables make subform1 and subform2 within FormA.

FormA contains A_ID(PK) and Date() field, subform1 has 3 fields, A_ID(Fk), Group, qty(quantities of cookies) , subform2 has 4 fields A_ID(FK), Group, Qty, Child

so when i insert data into subform1, such as Group = A, Qty = 15 then upon execution of append query i automatically have 3 rows filled as follows

Group = A, Qty = 6, Child = 1

Group = A, Qty = 6, Child = 1

Group = A, Qty = 3, Child = 1

and it stops, once quantity is ended. FYI, qty will never be decimal.

regards