Slow running query

[branston]

Hi,

I have a query which is based on 2 tables.
There are 28 columns in the query, 24 of which are calculations using fields in the 2 tables and 1 of which is a d-look up.

I had 1 link between the 2 tables and the query runs very quickly, however, when I add another link it takes AGES (currently at 5minutes and still counting) to run.

The 2nd link drops the number of records the query has to display from a hunge number (20,000 or something) to around 150. I don't understand why it takes to long to run!

Any ideas?

 

[vbaInet]

Why the two links between the tables? A properly normalized database would normally only need one even when running queries.

Plus ensure that the field you're linking via is indexed. Link fields with a datatype of Number run faster.

 

[branston]

the 1st link is on a field which shows which project the data is for (so there are many fields with the same value) and the 2nd links on the actual record (e.g. Record 1 for Project ACB)
Should I just link it on project and then group by record number?

 

[vbaInet]

Can't quite say yet. Let me see a screenshot of the tables and links in the query.

 

[branston]

Sure... how do I add attachments in here?!

 

[branston]

ah, don't worry - found it!

 

[branston]

See screen shots attached.
The code for "Cost01" in the query is repeated up tp "Cost24" each calling the corresponding hours.
If you need anything else let me know

 

[DCrake]

Why not add the query QryPaafMax to the query and do a outer join on the PaafNumber. Then for your invoice column

Invoice:Nz([PaafNumber],[ApproxRate],[InvoiceRate])

 

[branston]

Thanks DCrake - That is a much neater way of doing it!

 

[branston]

So, how come I can add links to another table and it works fine, but as soon as I try and link MppNumber the whole thing grinds to a halt?!

 

[vbaInet]

So, how come I can add links to another table and it works fine, but as soon as I try and link MppNumber the whole thing grinds to a halt?!

It could be linked to a couple of things: how the relationships have been set up, indexing and the datatype of the field.

 

[branston]

OK, thanks - I shall have a check through those points and see if there is anything I should change.