design query to calculate Opening & Closing Inventory (2 Viewers)

[illusionek]

I knew I was missing a simple trick here It worked like a charm.

I have done a subquery and applied iif statment and it worked!

On a different note, I ended up with a query where I make all these various calculations like OpeningStock, ClosingStock, PlannedProduction etc so in total have quite few columns with calculations.

It looks a lot like Excel now when I run this query, so I was wondering if I go wrong somewhere along the line or this is normal. I understand I should not store calculations in queries but was wondering if I should make one query per calculation or it is fine to keep all of them in one query?

 

[illusionek]

It looks like I did not get it right in the end.

I attached my database, can anyone please help me?

I need to get PreviousClosingInventory right in qryCalculations. At the moment it displays with 0 as I wanted to show how it will be linked back to other calculations in this query, especially OpeningStock

I have been trying to get this working since weekend so I am really desperate at this stage. :banghead:


Pls help

 

[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]

Please find attached a spreadsheet showing expected results of qryCalculations if everything worked well.

When it comes to starting data all is available in attached database.

My database got three tables: tblForecast with sales forecast, tblItemMaster with some master data related to products and tblOnHandStock with the stock figure at the beginning of the week.

Based on tblForecast and tblItemMaster I calculate what should be my stock target in qryTargetStock.

My 'main' query is qryCalculations that brings together data from the above three tables and qryTargetStock.

My issue/challenge is PreviousClosingInventory. This needs to show record from ClosingInventory for previous week. So in attached example, I would like to see 0 for 16/05/2016, 22800 for 23/05/2016, 22500 for 30/05/2016 etc.

I made many attempts with subqueries but I can't get it right, so I really need someone to guide me through this issue please

 

[plog]

You've built a house of cards there, I'm sure this could take a let less queries than what you've done. However, you can get there from what you have. Here's how:

Change the SQL of qryCalculations to this:

SELECT qryForecast.[Item Number], qryForecast.[Week Commencing], qryTargetStock.TargetStock, IIf(IsNull([OnHand]),0,[OnHand]) AS StockOnHand, IIf([qryForecast].[Week Commencing]=fCurrentWeek(),[StockOnHand],0) AS OpeningStock, IIf([TargetStock]-[OpeningStock]<0,0,[TargetStock]-[OpeningStock]) AS PlannedProduction, qryForecast.Forecast, [OpeningStock]+[PlannedProduction]-[Forecast] AS ClosingInventory, [qryForecast].[Week Commencing]-7 AS PreviousWeekCommence
FROM (qryForecast LEFT JOIN qryTargetStock ON (qryForecast.[Week Commencing] = qryTargetStock.[Week Commencing]) AND (qryForecast.[Item Number] = qryTargetStock.[Item Number])) LEFT JOIN tblOnHandStock ON (qryForecast.[Week Commencing] = tblOnHandStock.WeekCommencing) AND (qryForecast.[Item Number] = tblOnHandStock.[Item Number]);

Getting a prior value takes a subquery, that's what I have done with qryCalculations, it gets everythign set up so it can easily find the prior value you want. Then, you need this query to get the results you want:

SELECT qryCalculations.[Item Number], qryCalculations.[Week Commencing], qryCalculations.TargetStock, qryCalculations.StockOnHand, qryCalculations.OpeningStock, IIf([qryCalculations].[Week Commencing]=fCurrentWeek(),[qryCalculations].[OpeningStock],1*Nz([qryCalculations_1].[ClosingInventory],0)) AS ActualOpeningStock, qryCalculations.PlannedProduction, qryCalculations.Forecast, qryCalculations.ClosingInventory, 1*Nz([qryCalculations_1].[ClosingInventory],0) AS PreviousClosingInventory
FROM qryCalculations LEFT JOIN qryCalculations AS qryCalculations_1 ON (qryCalculations.PreviousWeekCommence = qryCalculations_1.[Week Commencing]) AND (qryCalculations.[Item Number] = qryCalculations_1.[Item Number]);

qryCalculations needs to be a subquery because otherwise you have circular logic:

PreviousClosing is based on ClosingInventory
ClosingInventory is based on Opening Balance
Opening Balance is based on Closing inventory

Let me know if you have any issues or questions.

 

[illusionek]

Thank you so much for helping me out.

Your queries work really well on a small dataset but I cannot run it on a larger dataset i.e. 7k-10k records. I get an error message about not enough memory. Is it because of a nature of the query or the way I did my database?

On this note you mentioned that I built a house of cards. Would you be able to elaborate on this and let me know what I could do better?

I was reading some online tutorials and the message I got from them was that I need to make sure my tables are normalized. As far as I can see my tables are normalized but to be honest I was getting an impression that my qryCalculations is not the right way to go. It reminds the Excel way but not sure what is the correct way of doing this type of calculations/scenarios in Access.

 

[plog]

The nature of this query (looking back to past records) means its going to require a lot of computation. However, I think you can optimize this with a few changes to your table and tightening up your queries.

Tables - You tables look good, but there's a few things you can do:

~Use a numeric foreign key. Right now your tables are linked via the text field 'Item Number'. You should have a table that has all your Items and assigns an autonumber primary key to them. That way you can use 1 to represent 'Product A', 2 for 'Product B', 3...

~Add indexes. Read up on them, they help speed up searches. For example, you should index your 'Week Commencing' fields because those are used to link your data.

~Name your fields better. This won't help performance, it just makes coding easier. You should only use alpha-numeric characters in field names. That means no spaces ([Week Commencing]=[WeekCommencing], [Item Number]=[ItemNumber])

Queries - You need to do more with less. With that last query I made, it takes you 8 queries to get you where you need to go. That can certainly be reduced. The more queries, the more computation it takes--especially for the queries on the bottom of the pile.

Here's what I do when I have a good understanding of the system as a whole:

~Make a map. Get a piece of paper, write the final query that gives you what you want on the left side of it, draw a circle around it. Then go into design view and see what data sources comprises it. For every datasource draw a line line from the initial query and then write the name of the datasource in a circle. Keep going down the line until you've mapped out the whole thing.

~Find the duplicated data sources and see if you can either reduce the number of times they appear, or have them appear closer to the left side of the page. For example, in your map qryForecast appears twice (in qryCalculations and in qryTargetWeeks). I don't know if that's possible, but that's how I would identify ones to focus on and what I would do to try and make this thing more efficient.

Looking back over your queries as I write this, it's not a house of cards. I saw all the queries and how they sometimes reference fields within themselves and figured it was a mess. It's not, it actually looks pretty well structured. I think you can make it a little better with my suggestions above, but whenever you look back and try to line up past data within a datasource itself, its always a resource hog.