Search results

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...

Hi guys Many thanks for looking into this post. Unfortunately since then I discovered that I have a more fundamental issue with this subequery than just a performance. For some reason I cannot get it right :banghead: I posted sample data in post #24 in the below thread if anyone would like to...

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...

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...

Unfortunately now it does not run at all with this join, I got error message about not enough memory.

I cannot check how many records are actually returned by this query but I would expect 7k-10k.

Hello I got this query with a subquery and it takes couple seconds to run. However if I try to go to the last record, export it to Excel via ExternalData tab or Make Table, my Access stops working and then I get an error message there is not enough memory. I am getting lost here as in the end...

I knew I was missing a simple trick here :D 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...

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...

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...

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...

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?

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...

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...

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...

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...

Many thanks for your code it works. However I wonder what do you mean by above? I do not store my calculations in tables, this is part of a query.

Would you be able to share a link or point me in the right direction where I can read more about good practice when it comes to Access. My background is not in databases and the only rule I really apply is to use normalized tables but I do not really know whats the good practice when it comes to...

It appears to be what I need but for some reason, it does not work when I apply it to my query. I attached a screenshot of my query and in second row I would expect to see 5104 in the last column. I use below query, would you be able to advise where I go wrong? SELECT Query1.Product...

Hello Can please someone point me in the right direction how to solve below problem? I have below table in my database where I have different products, sale date and sale value Product Name| Sale Date| Sale Value Product 1| 10/05/16| 100 Product 2| 10/05/16| 150 Product 1| 11/05/16| 125...