Filtering

[Elias Farah]

Hello All,

I hope someone can give me (and other keen access enthusiasts) some
helpful information to explain how to most efficiently filter Queries
& subqueies.

Consider this common simple situation:

1. OrderTable
OrderID - Indexed (PK)
VendorID - Indexed
etc

2. OrderLineTable
OrderLineID - Indexed (PK)
OrderID - Indexed
ProductID - Indexed
Price
Qty
TaxRate

3. VendorTable
VendorID - Indexed (PK)
VendorName etc.

4. ProductTable
ProductID - Indexed (PK)
ProductName etc

OrderLineQuery
OrderLineID
OrderID
ProductID
LineTotal: [Price]*[Qty]*[TaxRate]

OrderTotalQuery
OrderID
OrderTotal: Sum(LineTotal)
Now, If you want to query all Invoices, which include, say
ProductID=1000, and display the [OrderTotal], and perhaps Filter by
Vendor as Optional, and display[VendorName], you need yet another
Query to link the information.

Lets say there is 20,000 invoices, and 60,000 invoice lines, and
ProductID=1000 occurs on 12,000 invoices. Really, nothing too big!

Should I filter on ProductID:

a) OrderLineQuery Field, [OrderLineQuery].[ProductID]?

b) Should I create a Join between [OrderLineQuery].[ProductID] and
[ProductTable].[ProductID], and Filter
[ProductTable].[ProductID]?

Summing up the invoices can take 2 seconds, so it's inefficient to
have the OrderTotalQuery execute more than is required.

I don't want the OrderLineQuery to run too many times, because it
performing calcs on decimal currency, and tax rates - on 60,000 lines,
if unfiltered!

I am so confused, as I seem to be getting lost in the field
manifestation throughout the Subqueries - and when filtering more
fields simultaneously, ie, price, date, vendor, ordertotals etc etc -
The query can take 30 seconds, instead of a fraction of a second.
It's hard to explain.........

Look forward to someone giving me some good advice!
Regards

Elias Farah.

[Allen Browne]

Use the JOIN between the 4 tables. Access/JET is very good at choosing the
best query plan, and joins are generally the most efficient approach.

Include both the ProductID and VendorID in your query. You can add the
criteria as needed. In this way, the calculation is needed only for the
selected rows (rather than selecting and calculating all rows and then
culling them). For example, if this is for a form, you could assign the new
query statement (including the joins and criteria) to the RecordSource of
the form. I've generally found that reassigning a SQL statement dynamically
like that is much more efficient and flexible than subqueries or stacked
queries.

Your data structure looks good, though there is no need to index your
foreign keys. If you create relations involving referential integrity,
Access automatically creates indexes to manage the RI.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Elias Farah" <lo******@hotmail.com> wrote in message
news:c6**************************@posting.google.c om...

Hello All,

I hope someone can give me (and other keen access enthusiasts) some
helpful information to explain how to most efficiently filter Queries
& subqueies.

Consider this common simple situation:

1. OrderTable
OrderID - Indexed (PK)
VendorID - Indexed
etc

2. OrderLineTable
OrderLineID - Indexed (PK)
OrderID - Indexed
ProductID - Indexed
Price
Qty
TaxRate

3. VendorTable
VendorID - Indexed (PK)
VendorName etc.

4. ProductTable
ProductID - Indexed (PK)
ProductName etc

OrderLineQuery
OrderLineID
OrderID
ProductID
LineTotal: [Price]*[Qty]*[TaxRate]

OrderTotalQuery
OrderID
OrderTotal: Sum(LineTotal)
Now, If you want to query all Invoices, which include, say
ProductID=1000, and display the [OrderTotal], and perhaps Filter by
Vendor as Optional, and display[VendorName], you need yet another
Query to link the information.

Lets say there is 20,000 invoices, and 60,000 invoice lines, and
ProductID=1000 occurs on 12,000 invoices. Really, nothing too big!

Should I filter on ProductID:

a) OrderLineQuery Field, [OrderLineQuery].[ProductID]?

b) Should I create a Join between [OrderLineQuery].[ProductID] and
[ProductTable].[ProductID], and Filter
[ProductTable].[ProductID]?

Summing up the invoices can take 2 seconds, so it's inefficient to
have the OrderTotalQuery execute more than is required.

I don't want the OrderLineQuery to run too many times, because it
performing calcs on decimal currency, and tax rates - on 60,000 lines,
if unfiltered!

I am so confused, as I seem to be getting lost in the field
manifestation throughout the Subqueries - and when filtering more
fields simultaneously, ie, price, date, vendor, ordertotals etc etc -
The query can take 30 seconds, instead of a fraction of a second.
It's hard to explain.........

Look forward to someone giving me some good advice!
Regards

Elias Farah.