Cross reference ID's from 2 sets of data with unequal records in each

[futaba]

I have a scenarion where in a warehouse enviroment Pallets which have unique ID's containing fixed quantities of cartons are to be broken down to created new pallets with fewer cartons on each with new ID's.
I need to cross reference the old ID's with the new but want the minimum number of recors.
An example would be Dropping 5 pallets all containing 35 cartons and breaking these down onto 7 pallets all containing 25 cartons
The old ID's and the New ID's are pre set so I would have
Old ID Old QTY New ID New Qty
1 35 A 25
2 35 B 25
3 35 C 25
4 35 D 25
5 35 E 25
F 25
G 25
I need to end up with 11 records in this instance as follows
1 25 A 25
2 25 B 25
3 25 C 25
4 25 D 25
5 25 E 25
1 10 F 10
2 10 G 10
3 10 F 10
4 10 G 10
5 10 F 5
5 5 G 5
Mentally i would take the first new record qty from the first old record qty and if there was a positive balance left from the old record qty re create the same record at the end of the record set with the remaining balanceas the new qty and the same with the new id record if the old record qty is insufficient to fulfil the new pallet qty requirement write a new record with the same id and the remaining requirement as the balance.

I have no idea how to go about doing this in Access

Can Anyone help