illusionek
Registered User.
- Local time
- Yesterday, 23:40
- Joined
- Dec 31, 2013
- Messages
- 92
I am trying to design query or queries that will calculate my Opening / Closing Inventory and Production requirements.
I import data from Excel into a table that gives me Opening Inventory figure for the first week (16/05) only. I got a function that assigns current week commencing date when importing data.
I also import another set of data into a table to provide Demand value each week.
So my head is spinning now because I do not know how to design this one.
All above calculations require results of the other two. I just cannot get my head around this :banghead:
In Excel I would just link one cell to another and job done but I am missing the trick in Access :banghead:
The end result would like below and I need this to work for multiple products as well .
Please help
Product|WeekCommencing| Opening Inventory| Planned Production| Demand| Closing Inventory
Product A| 16/05/2016| 100| 20| 40| 80
Product A| 23/05/2016| 80| 35| 20| 95
Product A| 30/05/2016| 95
I import data from Excel into a table that gives me Opening Inventory figure for the first week (16/05) only. I got a function that assigns current week commencing date when importing data.
I also import another set of data into a table to provide Demand value each week.
Code:
Planned Production calculation is: Demand for the next 10 weeks - Opening Stock. I got a separate query calculating demand for the next 10 week
Closing Inventory calculation is: Opening Stock + Planned Production - Demand
Opening Inventory calculation is: Closing Inventory for previous week unless this is current week then value comes from imported table
All above calculations require results of the other two. I just cannot get my head around this :banghead:
In Excel I would just link one cell to another and job done but I am missing the trick in Access :banghead:
The end result would like below and I need this to work for multiple products as well .
Please help
Product|WeekCommencing| Opening Inventory| Planned Production| Demand| Closing Inventory
Product A| 16/05/2016| 100| 20| 40| 80
Product A| 23/05/2016| 80| 35| 20| 95
Product A| 30/05/2016| 95