VBA Alternative to a Slow Query Chain (3 Viewers)

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

 

[ChrisMore]

I've attached a test version of database with the queries and the test data. 11 queries in total and Inventory_Warning_Query is the record source of the report. Due to deleting a lot of data to be able to attach the file, the query is running fast. I'd say it usually takes around 15 seconds min and around 40 seconds max to run.

FYI, where ID criteria is entered on the queries Order_Shipment_Number_BOM_Products_Query and Inventory_Warning_Query, these fields would usually have Form reference as the criteria.

 

[olxx]

I think it's your "onhand" function that you use in query. The function itself runs five querys with joins. If you use this function in query, it will perform five querys for every record. I don't know how much data you usually have in your tables, but it can be a problem. Try running your querys manually, in order, one by one with your full data, and see wich one takes too long to respond.

 

[ChrisMore]

I think it's your "onhand" function that you use in query. The function itself runs five querys with joins. If you use this function in query, it will perform five querys for every record. I don't know how much data you usually have in your tables, but it can be a problem. Try running your querys manually, in order, one by one with your full data, and see wich one takes too long to respond.

Yes, I think you're right, it's slowest when the onhand function is involved.

I am unsure how to get around the onhand function because it is required when calculating the projected inventory. The whole thing can't work without the projected inventory being calculated.

 

[Minty]

This might be one of those instances where you are better off temporarily storing the calculated values as a temp table and then joining that to your other results.

Having had a closer look, I think you could create a query to produce the current stock quantity for all products with a bit of work, rather than the function, that would probably be much faster.

How many products are you calculating this for in the real world?

Happy to assist if you get stuck .

 

[ChrisMore]

I've created a temp table (zz_onhand_temp) and I've used an append query to add the data, which would have been the result of the 'Production_Units_BOM_Query1. I also included the onhand field to be appended. Of course it takes a few seconds to run the append query but not too bad. The Inventory_Warning_Query is running very fast now.

I'm left with this rather long code to run the append query:

strSql = "INSERT INTO zz_onhand_temp (Production_ID,Production_Date,Product_Code,BOM,onhand) " & vbCrLf & _
"SELECT Production_Schedule.Production_ID,Production_Schedule.Production_Date,Manufactured_Products_Junction.Product_Code,IIf([Products].[Product_Name] LIKE ""*Poly*"" OR [Products].[Product_Type_Code] = ""AD"", " & vbCrLf & _
"[Manufactured_Products_Junction].[Quantity] * [Production_Units],- Int(- [Manufactured_Products_Junction].[Quantity] * [Production_Units])) AS BOM, Round(OnHand ([Products].[Product_Code]), 5) AS [On Hand Quantity] " & vbCrLf & _
"FROM Products INNER JOIN (((Production_WOI_Query INNER JOIN Production_Schedule ON Production_WOI_Query.Ordered_Item_ID = Production_Schedule.Ordered_Item_ID) INNER JOIN Manufactured_Products ON Production_WOI_Query.Manufactured_Product_ID = Manufactured_Products.Manufactured_Product_ID) INNER JOIN Manufactured_Products_Junction ON Manufactured_Products.Manufactured_Product_ID = Manufactured_Products_Junction.Manufactured_Product_ID) ON Products.Product_Code = Manufactured_Products_Junction.Product_Code " & vbCrLf & _
"WHERE (((Production_Schedule.Production_Date) IS NOT NULL) AND ((Products.Product_Name) NOT LIKE ""*Stretch Wrap*"" AND (Products.Product_Name) NOT LIKE ""*packing tape*"") AND ((Production_Schedule.Production_Units) > 0) AND ((Manufactured_Products_Junction.Quantity) IS NOT NULL AND (Manufactured_Products_Junction.Quantity) > 0));"

What's the best way to update a temp table? The table will be updated on the AfterUpdate event of the Production_Date field.

 

[Minty]

I would only update it before you run the main process.
If the data isn't being used elsewhere, there is no point adding the overhead of keeping updated until you need it.

 

[ChrisMore]

I would only update it before you run the main process.
If the data isn't being used elsewhere, there is no point adding the overhead of keeping updated until you need it.

It will be needed every time the Production_Date field is updated. This is when the inventory warning check process is required. So it will append the data then open the report if a record is found in the Inventory_Warning_Query. The data will then need to be deleted after the process.

 

[Minty]

Hmmm - The most reliable way to make sure you capture the changes no matter where they occur would be a Data Macro. MS - Data Macro
They can run on a table change event, so that might be a way to achieve what you want.

Alternatively, if you are certain that data isn't updated anywhere else you could run a process on the after update event of that control.

I haven't had time to delve into your process properly, too busy with real work I'm afraid.

 

[RonPaii]

If the temp table has no primary key, any query using it will be read only. Fine for reports and lookups but a problem in a form.

 

[tvanstiphout]

For another perspective on inventory functions, take a look at the Northwind 2 Dev Edition template, and this video where Kim Young discusses the inventory module.

 

[TuurCarlsen]

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

If it's elaborate and/or complicated I use this method all the time.

Step one is creating a temp table with all kind of helpful fields (0 AS FieldNumeric, '' AS FieldText, etc.), as many as you like.

Don't forget the ID of your initial table.

Second step is starting a loop (recordset) and fill up those fields.

Many advantages:
- you can check each and every result in the temp table
- if it's wrong, no problem, search for the issue and correct it
- your initial table is untouched
- sometimes you encounter other issues afterwards
- maybe additional functionalities are necessary
- only when everything is right go to the next step

Third step is to update your initial table joining the temp table with the ID.

Fourth step is to drop the temp table.

Very important: use proper error handling. If any step fails, exit and check the issue.
Maybe it sounds weird, but a lot of issues are due to ... NULL values.

And finally: put this functionality in a module, so you can use it every single time.