design query to calculate Opening & Closing Inventory (1 Viewer)

[illusionek]

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.

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

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

 

[sneuberg]

Part of the solution might involve using a subquery as it appears you need data in one record that comes from another record. If you look at http://allenbrowne.com/subquery-01.html in the secton on "Get the value in another record" he gives example code. I usually just cut and paste that into an editor, e.g., Note Pad, and substitute my field values.

 

[plog]

Can you post sample data to demonstrate what you want? Provide two sets of data:

A. Starting data from all relevant tables. Include table\field names and enough data to cover all cases.

B. Expected result data based on A. Show what data should result when you start with the data in A.

 

[illusionek]

I can't figure this one out because I need PlannedProduction to calculate ClosingInventory and I need ClosingInventory to figure out OpeningInventory, which is then used in PlannedProduction as well. So all these figures depend on each other and I cannot grasp the concept how to do it :banghead:

Will be very grateful for any help can get.

Please find attached a sample database with data.

Demand table - provides data for demand for each week and 10wks Demand used to calculate Production
OnHand table - provides data for OnHand inventory for current week only
Results table - this is the end result I am trying to achieve.


Calculations in result table:

OpeningInventory: if current week(16/05) = OnHand othwerise =ClosingInventory from previous week

Demand10wks: from Demand table

PlannedProduction: Demand10wks - OpeningInventory

Demand: from Demand table

ClosingInventory: OpeningInventory + PlanndedProduction - ClosingInventory

 

[Minty]

You shouldn't keep importing the on hand figures, and shouldn't be storing the closing balance.

Your stock movements should be exactly that; an in / out record transaction.
Your opening balance (on hand) figures should go in as the first record - A positive transaction.
Your movements out are negative transactions in the same table.
Your current balance is the sum of the above transactions. You can then put your demand figures as a calculation against the current balance.
Over time you would also be able, using the transaction table, to calculate your average demand based on the historical stock movement all from the same table.

 

[CJ_London]

I'm rather busy today, but I helped the OP on this thread

http://www.access-programmers.co.uk/forums/showthread.php?p=1486528#post1486528

which might provide more background

 

[illusionek]

You shouldn't keep importing the on hand figures, and shouldn't be storing the closing balance.

I have no intention to store any calculations in tables. Result table is just to ilustrate what I am trying to achieve in terms of calculations and logic.

OpeningInventory does not always equal OnHand. This is true only for first week and then OpeningInventory becomes ClosingInventory from previous week for all other weeks. I have to import once a week OnHand, this is business requirment I cant ignore.

Your current balance is the sum of the above transactions. You can then put your demand figures as a calculation against the current balance.

The problem is that my current balance will not be sum of the above transactions as I need to include PlannedProduction as well which is positive figure but I cannot do it without knowing OpeningInventory and this one is based on PlannedProduction. So the way I see it it is a loop and I do not know how to fix it

I'm rather busy today, but I helped the OP on this thread

http://www.access-programmers.co.uk/forums/showthread.php?p=1486528#post1486528

which might provide more background

I read your previous post but I must be really thick as I am not sure how to apply this. I am able to get Demand figure that would be a negative value. I can get OnHand figure which would be a positive value for first week. But then I come back to my initial problems with calculating PlannedProduction, OpeningInventory and ClosingInventory as these calculations relay on each other

 

[Minty]

The problem is that my current balance will not be sum of the above transactions as I need to include PlannedProduction as well which is positive figure but I cannot do it without knowing OpeningInventory and this one is based on PlannedProduction. So the way I see it it is a loop and I do not know how to fix it

In really simple terms how do you do this now then. You must have had a starting point somewhere... Take this and draw out the process from that starting point and you may see where the hole in your current process is.

 

[illusionek]

I think the problem is caused by the fact that I import OnHand figure as OpeningInventory for the first week and then I want this to become a calculation from 2nd week onward. This works in Excel but not access.

Sadly I cannot disregard OnHand figure for the first week, so I am not sure how to solve it effectively in Access. It almost feels like I need to calculate all values for the first week only and then somehow link/append them to a query where based on them I would calculate values for all the other weeks. I am not sure if this makes sense :banghead: It would work because ClosingInventory from 1st week would become OpeningInventory for the second week and so on.
Any suggestions how to achieve this?

 

[Minty]

You may need a to create a "InitialBalance" Table and simply populate it with your starting values. I can't see any other obvious route around this.
With all transactional type processes you have to draw a line in the sand somewhere.

I'm still not sure that you are making it harder work than it needs to be.
The planned production figures can still be incorporated into the transactional calculations, just not sure how you would keep them updated using the current business model..

 

[illusionek]

I got a query calculating InitialBalance but I am not sure now how to link its results to use its value to calculate all other weeks. I have been working on this the whole morning :banghead:

Any suggestions please?

 

[Minty]

You are probably going to have to use a Union query to bring this all together.
So use your initial query - and format your addition calculations to have the same field layout or vice versa.
Then you can union the results into one query and perform your final sums , groupings etc on that.

 

[jdraw]

I think there is confusion on what you are actually trying to do.

I see PlannedProduction and OpeningInventory. What I don't see, and it may not be relevant, is ActualProduction.
To me a PlannedProduction suggests "I'm planning on using 200 units of X next week". However, at the end of next week, I may have actually used 198 units of X.

So any calculations have to be "firmed up" with the actual values (and the negative or positive balances accounted for in Inventory).

You seem to have a repeatable calculation, but each iteration varies by the Date or Planning Cycle.

Can you show/tell us the calculations/algorithms you are using --- plain English is fine?

It seems

ClosingInventory(wk)= OpeningInventory(WK) 
                             + PlanndedProduction(wk) 
                              - (plannedproduction -actualProduction)

where OpeningInventory(WK) = ClosingInventory(Wk-1)

 

[illusionek]

This is correct. There are no actual values here. The overall goal is to estimate my production values based on estimated sales and estimated stock position. We do not put actual values with one exception which I think causes all problems. My estimated ClosingInventory in Week 1 becomes Opening Inventory in Week 2. However my OpeningInventory in Week 1 is not ClosingInventory from Week 0. It is an actual stock on hand at the beginning of that week. Currently I import it into a seperate table in Access.

Calculations are below. I attached Excel file just to show how it works currently in Excel. It works well because I can change formulas in Excel within the same column but cant do the same in Access.

In yellow cells are values of OpeningInventory in Week 1 (16/05) as you can see for other weeks it is a calculation. In green columns are values of my demand and cumulative demand. I got a query that provides them so I left them in Excel as static value.

Is there any way to recreate this Excel logic in Access?

OpeningInventory: if current week(16/05) = OnHand othwerise =ClosingInventory from previous week

Demand10wks: from Demand table

PlannedProduction: Demand10wks - OpeningInventory

Demand: from Demand table

ClosingInventory: OpeningInventory + PlanndedProduction - ClosingInventory

Your above calculation is correct with the exception that I do not put actual production and Week 1 OpeningInventory is imported from the table whilst Week 2 is Closing Inventory from Week 1 as per attached Excel file.

 

[jdraw]

I maybe off base, but if you don't refine your ClosingInventory with the difference between Planned and Actual, I think you are perpetuating an Error/Variance.

Perhaps more details/examples showing your Demand values --what they really mean, and how they are being used and recorded-- may be helpful to readers.

 

[illusionek]

I literally import 2 Excel spreadsheet into Access tables. 1 table with 52 wks demand that contains Product Name, Week Commencing and Qty. 2nd table with OnHand figures for thr first week. I have one query that calculates next 10 wks demand and thats it. Based on them I am trying to perform calculations stated in previous post.

 

[jdraw]

This makes sense

However my OpeningInventory in Week 1 is not ClosingInventory from Week 0. It is an actual stock on hand at the beginning of that week.

At the point you start, there is no last week, so your current inventory is whatever is on-hand at the start.

There is another point I'd like to mention.

If you can't write down the logic of what you need, then you can't program it with vba or any other language. Not Access nor Oracle etc can magically solve the issue. The programming language only does what you instruct it to do.

 

[illusionek]

I am sorry I am getting really confused. I can write down the logic as a matter of fact it is a simple one. I was also able to successfully apply this logic in previously attached Excel file. I just simply struggle to do the same in Access.

The logic is following:

If I am in Week 1 then:

OpeningInventory = OnHand
PlannedProduction = Demand10wks - OpeningInventory
ClosingInventory = OpeningInventory + PlannedProduction - Demand

*Demand and Demand10wks come from a query so do not need to be calculated

If I am in Week 2 then:

OpeningInventory = ClosingInventory Week 1
PlannedProduction = Demand10wks - OpeningInventory
ClosingInventory = OpeningInventory + PlannedProduction - Demand

And so on. I just simply do not know how to do it in Access.

Would you be able to help please? I just really need to see an example of query that would handle the above scenario and then I could hopefully work with this.

I understand I need to use a subquery to get data from the previous week but where I get lost is how to write a query that takes OnHand as OpeningInventory for Week 1 and then takes ClosingInventory Week 1 as OpeningInventory for Week 2. :banghead:

If I am able to get that then I think all my problems would be solved, well apart from the fact I need to spend more time learning Access

 

[sneuberg]

I understand I need to use a subquery to get data from the previous week but where I get lost is how to write a query that takes OnHand as OpeningInventory for Week 1 and then takes ClosingInventory Week 1 as OpeningInventory for Week 2.

I suggest getting the subquery working first. Put that in its own column let's say PreviousClosingInventory. If the subquery is working ok you should be getting Null for the first week as there was no previous week. You can use that fact in a IIF function (see http://www.techonthenet.com/access/functions/advanced/iif.php ) to determine which value the field OpeningInventory gets.

If you wait long enough maybe arnelgp will pick up on this thread and write the query for you.

 

[jdraw]

Based on your post 18, I think this applies

If this is the startingweek, then
     OpeningInventory =  InitialStockOnHand
ELSE
     OpeningInventory = ClosingInventoryPreviousWeek 
End if

Now we need to understand the details of Demand10Wks and Demand