0down votefavorite
Way out of my depth here - hoping to get help.
I use Access to arrange 3 sets of data. Promise Date, Delivery Date and Production Date to look for On Time Delivery, On Time Production, Etc.
There are no links that pair these 3 tables. We might build 5 pieces and ship 3 to one and 2 to another, so I can't do a lookup based on quantity movement.
Instead, I break each table into "Eaches" by date and match them in a FIFO fashion.
Thus the first unit produced probably belongs to the first unit shipped, etc.
This requires a tremendous amount of manual manipulation, primarily sorting by date and putting back into Access.
Is there a way to take a table, break it into eaches, and sort & count that table by date?
It would take this:
Prod / Date / Qty X / Jan-10 / 2 Y / Feb-10 / 2 Z / Mar-10 / 2
And turn it into this:
Prod / Date / Qty / Order X / Jan-10 / 1 / 1 X / Jan-10 / 1 / 2 Y / Feb-10 / 1 / 1 Y / Feb-10 / 1 / 2 Z / Mar-10 / 1 / 1 Z / Mar-10 / 1 / 2
I could link the tables together from there - but can't figure out how to do it outside of Excel sorting and formulas that loWay out of my depth here - hoping to get help.
Way out of my depth here - hoping someone can help me.
I use Access to arrange 3 sets of data. Promise Date, Delivery Date and Production Date to look for On Time Delivery, On Time Production, Etc.
There are no links that pair these 3 tables. We might build 5 pieces and ship 3 to one and 2 to another, so I can't do a lookup based on quantity movement.
Instead, I break each table into "Eaches" by date and match them in a FIFO fashion.
Thus the first unit produced probably belongs to the first unit shipped, etc.
This requires a tremendous amount of manual manipulation, primarily sorting by date and putting back into Access.
Is there a way to take a table, break it into eaches, and sort & count that table by date?
It would take this:
Prod / Date / Qty
X / Jan-10 / 2
Y / Feb-10 / 2
Z / Mar-10 / 2
And turn it into this:
Prod / Date / Qty / Order
X / Jan-10 / 1 / 1
X / Jan-10 / 1 / 2
Y / Feb-10 / 1 / 1
Y / Feb-10 / 1 / 2
Z / Mar-10 / 1 / 1
Z / Mar-10 / 1 / 2
I could link the tables together from there - but can't figure out how to do it outside of Excel sorting and formulas that look at rows above.