Limiting Microsoft Access query

jrmcanada2
jrmcanada2 used Ask the Experts™
on
I have a client with an Access-based inventory system with very complicated pricing. The pricing is based on a query that performs a lot of calculations based on several tables. The pricing query is set up to work on all the records in inventory (about 50,000 items).

This query is used in pretty much every inventory report.

The problem I have is that even if a report has only a handful of items on it, the underlying pricing query seems to be calculating pricing for every item in the database meaning that every report is now becoming immensely slow.

As a test, I ran a report of all items for a vendor and I chose a vendor that only has 5 items. It was extremely slow. Then I deleted every item in the database except those 5 and the report was lightning fast.

In this example, the report is based on a parent query that pulls vendor and item data (including the pricing query) and the parent query has a record source that specifies just the one vendor. However, it appears that the pricing query is running on every item in the database and then subsequently being filtered based on the parent query.

How do I get Access to only run the child query (i.e. the pricing query) for the items that are on the report? I should add that the pricing query is a child query in dozens of other reports in the database so I'm hoping for a solution that isn't going to require a lot of coding dozens of times over.

Thanks in advance.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
You must add Filtering.
instead of using the entire table and Finally extract the pricing info for the 5 items ..you Start with filtering the input table.
Open the query in design view.
Locate this particular table...it might be nit straight forward as it could be 'burried' under other queries and so on.
When you find this particular table just replace it with a parameterized query..(Create a simple query that Selects all the Items that belong to a vendor)...
After that replacement everytime you run a report you will presented with a Pop up to enter the Vendor ID(or whatever you want)..
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You will have to remove the pricing calculation from the lower levels to the final stage - the source that drives the report.
You will have to create a final query for this report only and leave the current for the other usages you have.

Without your queries, we can't be specific.
Top Expert 2014

Commented:
please post a representative sample of the database (the small table test version might do)
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ste5anSenior Developer

Commented:
Just a comment:

Pricing should be a separate process like commitments in invoice processing. Cause prices must be stable in many scenarios, e.g. an offer must be printed using the same prices during the offer period.

For the performance of your process:

As the others already pointed out, without knowing the data model and the calculus itself, it's hard to give advice.

The only thing right now is:

When you want/need to stick to the current approach, then you need as primary key in all involved tables a partition key. Thus is primarily the VendorID as first column. Then you need to filter on the vendor and use it in the JOIN conditions as well. This can mean that you may need also an additional indices on your existing tables. Check whether all foreign key relationships involved in this calculus are fully indexed (indices existing on the child-side on the foreign key column(s)).
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
You might try a technique I use in many of my databases.

1.  Create a new table (tblSelectedItems) in your database (put this in the front-end, not the BE).
2.  Add an ID column (long integer, primary key) to this table (I also add a IsSelected Y/N) field to mine so that I can use it in subforms.
3.  Before running the query to get your final results, insert the ID values of all of the products you want to evaluate into this table.  Something like:
currentdb.Execute "DELETE FROM tblSelectedItems", dbfailonerror
strSQL = "INSERT INTO tblSelectedItems (ID, IsSelected) " _
       & "SELECT ItemID from tblVendorItems WHERE VendorID = 3"
currentdb.Execute strSQL, dbfailonerror

Open in new window

4.  Then, in the base query, lowest level of the pricing query, add this table to the query and join it to some other field that contains those same ItemID values.  This will effectively filter the pricing recordset at the earliest point in time, preventing it from evaluating all of the pricing computations for those items not in tblSelectedItems.

Author

Commented:
Thanks for the input.

I've attached a sample database. Forget I ever mentioned pricing. :) This one has to do with weights. Here's the overview.

I have a table of lots where each lot is for a specific vendor. Each lot also has containers (possibly multiple containers) associated with it.

Each container has a wet weight and a moisture. I have a query (qryWeights) that calculates the dry weight for each container.

Finally, I have a query that shows all the lots for a specified vendor and includes the total dry weight for each of those lots.

This is a massively simplified version of what the live database actually does but it illustrates my problem.

In the sample database, qryWeights is a child query of qryVendor. But when I run qryVendor, qryWeights calculates the DryWeight for every lot in the database even though maybe only one or two lots are selected by qryVendor.

What I'm looking for is a way to open qryVendor so that the calculations in qryWeights are only done for the lots that qryVendor selects.

Thanks again!
sample.accdb
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
As I see it, one query will do:

SELECT 
    tblLots.LotNumber, 
    tblVendors.VendorID, 
    Sum([WetWeight]*(100-[Moisture])/100) AS DryWeight
FROM 
    (tblVendors 
INNER JOIN 
    tblLots 
    ON tblVendors.VendorID = tblLots.Vendor) 
INNER JOIN 
    tblLotsContainers 
    ON tblLots.LotNumber = tblLotsContainers.LotNumber
GROUP BY 
    tblLots.LotNumber, 
    tblVendors.VendorID
HAVING 
    tblVendors.VendorID = 1;

Open in new window

ste5anSenior Developer

Commented:
Yes, sub-queries are evaluated first. And your weight query doesn't know at that moment that it will be filtered. E.g.

SELECT   L.LotNumber ,
         L.Vendor ,
         SUM(LC.[WetWeight] * ( 100 - LC.[Moisture] ) / 100) AS DryWeight
FROM     tblLots L
         INNER JOIN tblLotsContainers LC ON L.LotNumber = LC.LotNumber
WHERE    L.Vendor = 1
GROUP BY L.LotNumber ,
         L.Vendor;

Open in new window

Top Expert 2014

Commented:
@Gustav

I think your Having clause should be a Where clause.  You don't reference anything in the grouped aggregation(s).

Author

Commented:
Thanks again for the continued input.

The actual weight query is much more complicated and used in maybe 100 places in the database. So eliminating it by rewriting the parent queries to do the calculations themselves isn't really an option for me. (Among other things, I don't want to try to keep the calculations consistent in every instance when I have to change something. It's much safer to have one query that is called from everywhere so that consistency is guaranteed.)

Thank you, ste5an, for confirming that sub-queries are evaluated first.

What I'm wondering now is how can I make the sub-query execute for only specific lots? If I go into the sub-query and add specific lot numbers in the criteria field, it works perfectly. But that's just for testing. In reality, since that query is used in 100 places and they have different ways of filtering the lots every time, I can't hardcode a reference to lots in the sub-query.

I have a couple ideas:

Option 1
I could have a local working table that I write the selected lot numbers to and have the weights query point to that to get the criteria to filter its lot numbers. One of the problems with this approach is that a user could simultaneously be using several features all of which use the weights query and I wouldn't want to confuse the lot numbers between the features.

Option 2
Is it possible to create a temporary or virtual query from my weights query and then add the lots I want as criteria for the LotNumber field in that virtual query and then use that for my report?

But I'm wondering if there's a better way to force the sub-query to only use certain lots. I've tried playing with the WHERE clause of the parent query, but with no success so far.

James
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
I think your Having clause should be a Where clause.  You don't reference anything in the grouped aggregation(s).

I (or rather the questioneer) do. Vendor ID is selected.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
But I'm wondering if there's a better way to force the sub-query to only use certain lots.

I'm not sure what that means. Anyway, the "trick" is to filter the lots to those needed, then perform the heavy calculations with these only - as my query does.

Author

Commented:
Hi Gustav,

Sorry for being slow on the up-take ... I just want to make sure I understand you correctly.

I currently have approximately 100 different queries (for various reports, forms, etc.) that all have the weights query as a sub-query.

Are you saying I should move all the calculations from the weights query into each of those queries? Or is there a way for me to leave the calculations in a sub-query and filter that sub-query so that only the relevant records are calculated?
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Yes, the joins (and other filters) will do the filtering so calculations only need to be done on the final records.
How to implement it in detail is difficult to tell, as we don't have your application.
Top Expert 2014

Commented:
Will the lots be determined in some consistent way, such as the result of the vendor query?
ste5anSenior Developer

Commented:
Well, depending on the actual use-case and requirements, consider precalulating the weights.

Author

Commented:
Regrettably, the lots are not determined in a consistent way. Almost every report has different criteria for selecting lots.

Precalculating the weights is on my list of possible fixes. :)
John TsioumprisSoftware & Systems Engineer

Commented:
Well the idea of local working table is good.
You could filter the Lots like this
... WHERE LotID IN ( SELECT LotID FROM LotsRepo_Table)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial