VBA Alternative to a Slow Query Chain (1 Viewer)

[ChrisMore]

Hi all,

I have a query named Inventory_Warning_Query which is designed to display the products that have an inventory value projected to fall below zero, when factoring in a new production date. The results are shown in a report and act as a warning when the production date is entered so it can be amended to a suitable date when the required stock is next available. This query is part of a chain of queries (shown in the attachment) and it does work as intended, however it is quite slow to run. I know that the speed to run these queries will be a problem when users start to use it so I think I need to go a different route and use VBA instead.

The queries can be broken down to the following steps:

1. Identify the product codes relevant for use in production of the shipment in question
2. Filter the table containing the bill of materials (BOM) data for all products to only include products relevant for use in production (these first two steps were included to speed up the process and are not essential)
3. Calculate the BOM data for all shipments using the results from step 2 (or all product data if not including the first two steps)
4. Identify the maximum production date for all products found in step 3
5. Identify the goods in records where the due in date is less than or equal to the max production dates found in step 4
6. Sum the goods in records from step 5
7. Sum the BOM data from step 3
8. Identify the goods in records where the due in date is greater than the max production date
9. Calculate the projected inventory for each product code using the summed values from step 6 and 7
10. Merge the projected inventory information (step 9) with postproduction goods in data (step 8) where applicable
11. Inventory_Warning_Query - only includes records where the projected inventory is less than zero. Includes the production record ID referencing the form where the production date is entered. Includes the BOM data relevant to production so the required quantity can be included in the report, alongside the projected inventory value

If this could be further simplified as queries that will run faster then I'd be happy with that route but I haven't been successful in doing so. Unfortunately this function is above my VBA knowledge so I'd be very grateful for any support I can get.

Thanks so much,
Chris

 

[arnelgp]

i don't know if those entities on your diagram are individual queries, but you can first create, on each table, aggregate by product code and just link the result via this product code.

you build small Total queries first, then join them on common product code.

 

[plog]

My gut and my experience compels me to first want to know the details of this query chain and your table structure. I've come across complex query systems like this before and have always found inefficiencies.

Can you post a sample database with the necessary queries and tables to feed them along with some sample data? Doesn't have to be a ton of data, doesn't have to actually trigger the slowness. Just want to see the details of what you have now.