VBA Alternative to a Slow Query Chain (5 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.

 

[ChrisMore]

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.

This is more like what I had in mind when I created this thread, so thanks for suggestion it. What I've done so far by appending the data to a table and then deleting that data feels clunky and not optimal in the slightest.

I've reattached the database and data, but this time have included the code and forms I use to run the code. First click the button to open the form. Then in the Production Date field, re-enter the date 05/08/25 for ID '69921'. This will run the append process and open the inventory warning report.

Could someone be kind enough to help me with writing the code so I'm avoiding appending and deleting data every time the date field is updated? The above guide is helpful but due to my limited knowledge of temp tables and recordset looping, I require more detail to create a working function.

Many thanks.

 

[arnelgp]

there is no daily_staff table.
suggest you add Index on all Fields/tables used in your Criteria on the SQL string in your Quantity_On_Hand routine.

also you add vba code that will open a "persistent" connection to your backend.
this will somewhat make your db faster, specially on multi-user environment.

 

[ChrisMore]

there is no daily_staff table.
suggest you add Index on all Fields/tables used in your Criteria on the SQL string in your Quantity_On_Hand routine.

also you add vba code that will open a "persistent" connection to your backend.
this will somewhat make your db faster, specially on multi-user environment.

Hi @arnelgp, I have added the missing tables and reattached, but these are only required when updating the Production Units field and not the Production Date field. I should have removed the code requiring these tables because it's not relevant to the inventory warning process I am trying to optimize.
Forgive me, but I am unsure what you mean about adding Index to fields/tables and why this is needed. I am self-taught with Access and VBA so this is a new one to me.
I will certainly look into persistent connections as this is also a new one to me.

 

[arnelgp]

adding Index will speed up finding records on your table.
take this snippet code from your function:

        'Get the last stocktake date and quantity for this product.
        If Len(strAsOf) > 0 Then
            strDateClause = " AND (StockTake_Date <= " & strAsOf & ")"
        End If
       
        strSQL = "SELECT TOP 1 Stocktake_Date, Product_Quantity FROM Stocktake " & _
            "WHERE ((Product_Code = " & lngProduct & ")" & strDateClause & _
            ") ORDER BY Stocktake_Date DESC;"

you need to add Index on Product_Code, another index on StockTake_Date to table Stocktake.
if you already have indexes on these fields, just go to another table in your code
and do the same.
it will make your select query much faster when retrieving records from your query.

attached is your FE, i added a form, frmHidden that opens persistent connection to the backend.
the form is opened via AutoExec macro (autoexec macro is the first macro being executed
when your app opens after your startup form). see the Load and Unload event of this form.
on the Unload event, it closes the connection (database) of the backend.
try incorporating these to your production db and see if there are any changes in speed.

 

[ChrisMore]

adding Index will speed up finding records on your table.
take this snippet code from your function:

        'Get the last stocktake date and quantity for this product.
        If Len(strAsOf) > 0 Then
            strDateClause = " AND (StockTake_Date <= " & strAsOf & ")"
        End If
 
        strSQL = "SELECT TOP 1 Stocktake_Date, Product_Quantity FROM Stocktake " & _
            "WHERE ((Product_Code = " & lngProduct & ")" & strDateClause & _
            ") ORDER BY Stocktake_Date DESC;"

you need to add Index on Product_Code, another index on StockTake_Date to table Stocktake.
if you already have indexes on these fields, just go to another table in your code
and do the same.
it will make your select query much faster when retrieving records from your query.

attached is your FE, i added a form, frmHidden that opens persistent connection to the backend.
the form is opened via AutoExec macro (autoexec macro is the first macro being executed
when your app opens after your startup form). see the Load and Unload event of this form.
on the Unload event, it closes the connection (database) of the backend.
try incorporating these to your production db and see if there are any changes in speed.

Thanks for the attached, that's very helpful.

I've incorporated everything you suggested and it definitely has made the process much quicker. Everything is running much more smoothly across the whole Db, so thanks for the suggestion.

Regarding my current set up where I am appending and then deleting data after every Production Date update, will this cause the backend file to balloon in size until I run a Compact & Repair?

I've got a niggling feeling that this set up will cause issues down the line after implementation, such as when multiple users happen to update a Production Date field at the same time. This would mean more than one append occurring before the data is deleted, so when querying the data after the append but before the deletion, the result will be incorrect.

 

[arnelgp]

you can try moving the zz_onhand_temp table to the FE (each FE) or move it to another Backend.

i did look at your Inventory_Warning_Query, and Production_ID and Product_Code from zz_onhand_temp table is being linked.
so i think you only need to insert these 2 fields in your Insert Query code and just leave out all the rest of the fields (and calculated ones).
all other fields are not required since on your code, you are only checking if there is record in inventory_warning_query.