Recent content by illusionek

  1. I

    design query to calculate Opening & Closing Inventory

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

    subquery - not enough memory

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

    design query to calculate Opening & Closing Inventory

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

    design query to calculate Opening & Closing Inventory

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

    subquery - not enough memory

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

    subquery - not enough memory

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

    subquery - not enough memory

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

    design query to calculate Opening & Closing Inventory

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

    design query to calculate Opening & Closing Inventory

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

    design query to calculate Opening & Closing Inventory

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

    design query to calculate Opening & Closing Inventory

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

    design query to calculate Opening & Closing Inventory

    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?
  13. I

    design query to calculate Opening & Closing Inventory

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

    design query to calculate Opening & Closing Inventory

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

    design query to calculate Opening & Closing Inventory

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