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" <longnose@hotmail.com> wrote in message
news:c6cfe5a6.0311030555.5ed42bd7@posting.google.c om...[color=blue]
> 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.[/color]
