How to speed up my Query

[Cfish3r]

Hi all,



I have the following set of queries, and when I try to view or export the 'CrashQuery' it takes at least 5-10 minutes to process, or it simply locks up my entire computer. Is there an obvious way that I can increase the efficiency and speed this up?

Any help is really much appreciated!

Query Fields:

My XeroxRequest table has roughly 1,000 records at most, and the SaData table has roughly 6,000 records.

Please see the below screenshots for reference.

xTrimSerial: Trim(Left([Serial Number],10))

UsageConverted: IIf([Usage Function]="ZBWT","M",IIf([Usage Function]="ZC1M","M",IIf([Usage Function]="ZCOT","C",IIf([Usage Function]="ZC2T","C",IIf([Usage Function]="ZC3T","C",IIf([Usage Function]="ZC3M","E",""))))))

FDate: Replace([Reading Date],"/",".")

TrimSerial: Trim(Left([Serial No],10))

Meter2match: IIf([Stock meter desc]="Colour","C",IIf([Stock meter desc]="MONO","M",IIf([Stock meter desc]="Black & White","M",IIf([Stock meter desc]="EXPRESSIVE","E",IIf([Stock meter desc]="expressive colour","E","N")))))

Expr1: Current meter Reading

Screenshots for relations etc.

[June7]

Must be something about the table joins because nothing unusual about the nested IIf expressions.

UsageConverted and Meter2match fields don't sound like same data but if you say they are then must accept.

Why replace / with . in date value?

Could have 'lookup' tables for the Usage Function and Stock meter desc codes. Maybe table joins will be faster than the nested IIf expressions.

Sometimes an alternative to nested IIf is Switch() or Choose() function.

If you want to provide db for analysis, follow instructions at bottom of my post.

[Cfish3r]

Well, running the first two queries takes little to no time whatsoever, it is only the crash query that takes time.
I will try using a lookup table however, since that does sound like a cleaner method.

I would love to provide the database for analysis, but virtually all the data in it is fairly confidential.

The reason I change the / to . in the date function, is because the report that the query produces, is uploaded to website that then processes it and imports it to another database which requires it explicitly in that format.