Speed up queries with relationships

[heathlovell]

Hello,
I have two tables with at most 700 records. I have a one - to - many relationship between the tables. Basically, one table is Purchase Order information and the other contains the line items for the Purchase Order with the Purchase Order number being the relationship between the two tables. I can open both tables very quickly. However, when I run a query, it is taking about 4 minutes. The query takes a date range as a parmater and returns the lines that fall within the date range. Does anyone know why this is so slow and /or how I could speed it up? These tables will only grow as time goes on.
Thanks for your help,
Heath Lovell

 

[randysmid]

Hi,
Here is an experiment for you to try with a stopwatch.

Split this into two separate queries. The first query should just retrieve only those records that fit within the desired date range (qryPODateRange). The second query (qryPODateRangeFinal) will use this query (qryPODateRange) and the Purchase Order table as it's source.

The concept is to quickly jump into the PO Detail table and retrieve only those records that match the date range. Then, it is a simple matter to link these records to the PO master table. HTH,
Randy Smith
California Teachers Association