Limiting Microsoft Access query

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

John TsioumprisSoftware & Systems EngineerCommented:
You must add Filtering.
instead of using the entire table and Finally extract the pricing info for the 5 items Start with filtering the input table.
Open the query in design view.
Locate this particular 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)..
Gustav BrockCIOCommented:
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.
please post a representative sample of the database (the small table test version might do)
C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

ste5anSenior DeveloperCommented:
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 LLCCommented:
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.
jrmcanada2Author 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!
Gustav BrockCIOCommented:
As I see it, one query will do:

    Sum([WetWeight]*(100-[Moisture])/100) AS DryWeight
    ON tblVendors.VendorID = tblLots.Vendor) 
    ON tblLots.LotNumber = tblLotsContainers.LotNumber
    tblVendors.VendorID = 1;

Open in new window

ste5anSenior DeveloperCommented:
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 ,

Open in new window


I think your Having clause should be a Where clause.  You don't reference anything in the grouped aggregation(s).
jrmcanada2Author 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.

Gustav BrockCIOCommented:
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.
Gustav BrockCIOCommented:
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.
jrmcanada2Author 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?
Gustav BrockCIOCommented:
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.
Will the lots be determined in some consistent way, such as the result of the vendor query?
ste5anSenior DeveloperCommented:
Well, depending on the actual use-case and requirements, consider precalulating the weights.
jrmcanada2Author 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 EngineerCommented:
Well the idea of local working table is good.
You could filter the Lots like this
... WHERE LotID IN ( SELECT LotID FROM LotsRepo_Table)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.