Filtering a sub-query (back-end is MS SQL)

[James Martin]

I have a database that has an Access front-end and a MS SQL back-end. It has
a report that is based on a query of the inventory table. The query
(qryInventoryTransfer) also has a sub-query (qryInventoryWeights) that does
some calculations for weights. When I call the main query, I limit it to
just the necessary inventory items for that report (there are typically
fewer than 10 inventory items out of 10,000). For sake of simplicity, let's
say I just want one item:

SELECT * FROM qryInventoryTransfer WHERE ItemCode = 123456

My problem is that even though the main query is limited to just that one
inventory item, the subquery processes all 10,000 items in inventory and so
the report is immensely slow ... it takes 15 minutes to run.

If I go into the design of the sub-query and limit it to just item 123456,
the report takes a couple seconds to run.

I did some experimienting and even if I limit the main query in design mode
to just item 123456, if the sub-query doesn't have a limit, the report takes
15 minutes. I would have guessed that Access would only call the sub-query
for the associated lots on the main query, but clearly I'm wrong.

What's the best way to handle this?

Thanks in advance!

James

P.S. The sub-query is used throughout my app for different reports and
forms, so I'd like to have a solution that doesn't limit it to this one
report, but perhaps there isn't one.

 

[John Spencer]

Could you post the entire query? It is possible that it could be made more
efficient, but without knowing any details it is difficult.

One method if you are doing all this in access would be to use a two query
approach and apply the criteria to both queries.

So subquery reads something like
SELECT ItemCode, Field1*Field2 as Multiply, Field3-Field4 as Subtract
FROM Whereever
WHERE ItemCode = Forms!MyForm![Specify Item Code]

Then use that saved subquery
SELECT qryInventoryTransfer.*, Multiply, Subtract
FROM qryInventoryTransfer Inner Join QueryCalculations
ON qryInventoryTransfer.ItemCode = queryCalculations.ItemCode
WHERE qryInventoryTransfer.ItemCode = Forms!MyForm![Specify Item Code]

A second method would be to use a correlated sub-query in the select clause if
you only need qryInventoryWeights to calculate one result per record in
qryInventoryTransfer. If you needed a couple values you could use two
correlated sub-queries.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County