Hello,
I have two tables in my database.
The first one is chronologically structured as follows:
The second one is also chronological and has the following structure:
How does a procedure look like now, which sums up all production quantities in the actuals table with material ID 300, which were produced 14 days before the planned production date up to the planned production date, and outputs them in a new column in a new table?
In addition, however, there should be another column that gives out the sum of the quantities that were actually produced in the following 5 days.
But now comes the crux: These two calculations (period before and after) are supposed to 'fill up' the planned quantity and pass on the surplus to the next MaterialID 300, if the actual production date of the surplus falls into one of the two periods (before or after) of this next MaterialID 300. If there is no next MaterialID 300 with overlapping periods, no proportional quantity should be passed on and if there is a shortage, there is nothing to pass on anyway.
In a nutshell: I just want to track how many actuals were produced in the 14 days period before planning date and on the planning date in one column and how manys actuals were produced in the 5 days frame after the planning date in another column. This, however, with the condition that actually produced surplus quantities are passed on to the next identical MaterialID only if the date of the actually produced surplus quantity falls within its two time windows.
So the final table would look like this:
How do I implement this in VBA in Access?
Kind regards
Sabrina
I have two tables in my database.
The first one is chronologically structured as follows:
MaterialID | PlannedProductionDate | PlannedProductionQuantity |
255 | 27.12.2021 | 192 |
255 | 04.01.2022 | 192 |
300 | 15.01.2022 | 94 |
300 | 12.04.2022 | 188 |
300 | 19.04.2022 | 94 |
87 | 19.06.2022 | 10 |
The second one is also chronological and has the following structure:
MaterialID | ActualProductionDate | ActualProductionQuantity |
255 | 31.12.2021 | 200 |
255 | 05.01.2022 | 5 |
300 | 12.01.2022 | 100 |
300 | 11.04.2022 | 500 |
How does a procedure look like now, which sums up all production quantities in the actuals table with material ID 300, which were produced 14 days before the planned production date up to the planned production date, and outputs them in a new column in a new table?
In addition, however, there should be another column that gives out the sum of the quantities that were actually produced in the following 5 days.
But now comes the crux: These two calculations (period before and after) are supposed to 'fill up' the planned quantity and pass on the surplus to the next MaterialID 300, if the actual production date of the surplus falls into one of the two periods (before or after) of this next MaterialID 300. If there is no next MaterialID 300 with overlapping periods, no proportional quantity should be passed on and if there is a shortage, there is nothing to pass on anyway.
In a nutshell: I just want to track how many actuals were produced in the 14 days period before planning date and on the planning date in one column and how manys actuals were produced in the 5 days frame after the planning date in another column. This, however, with the condition that actually produced surplus quantities are passed on to the next identical MaterialID only if the date of the actually produced surplus quantity falls within its two time windows.
So the final table would look like this:
MaterialID | PlannedProductionDate | PlannedProductionQuantity | ProducedWithin14DaysWindowBefore | ProducedWithin5DaysWindowAfter | |
255 | 27.12.2021 | 192 | 192 (from 31.12.2021) | ||
255 | 04.01.2022 | 192 | 8 (from 31.12.2021 pro rata) | 5 | |
300 | 15.01.2022 | 94 |
| ||
300 | 12.04.2022 | 188 | 188 (from 11.04.2022 pro rata) | ||
300 | 19.04.2022 | 94 | 312 (pro-rated from 11/04/2022; Surplus cannot be passed on.) | ||
87 | 19.06.2022 | 10 | 0 | 0 |
How do I implement this in VBA in Access?
Kind regards
Sabrina