Any ideas on how to get this query to perform faster?????

[INeedAccessHelp]

Now it takes about 3 minutes. The users are frustrated as you may imagine. Here is the SQL:

SELECT tblDist.DID, tblDist.SKU, tblDist.Sequence, tblDist.[MSA Brand Code]
FROM tblDist
WHERE tblDist.DID Like [Forms]![frmDetail]![DID] AND tblDist.[Resolved?]=False AND tblDist.[Verified?]="UnVerified" AND tblDist.[Completed?]=False AND IIf(Left([Report Title],4)=Left([Forms]![frmDetail]![PNAME],4),True,False)=True OR tblDist.DID Like [Forms]![frmDetail]![DID] AND tblDist.[MSA Brand Code] Like "9999*" AND tblDist.[Resolved?]=False AND tblDist.[Verified?]="UnVerified" AND tblDist.[Completed?]=False;

ANY help would be greatly appreciated. Thanks in advance.

--Rob

 

[cmmrfrds]

What version of Access? Linked tables or not? How many records in the tblDist table? Are you running off a network drive or local PC? Is this a front end / back end setup?

 

[INeedAccessHelp]

The "tblDist" table holds about 36,000 records. I'm using Access 2000 with linked tables, the tables are in an Access 97 dbase (but the efficiency is just as bad in the Access 97 dbase), the dbase is running on a network (my test version on my local machine is just as slow). Hope these answers help.

 

[cmmrfrds]

WHERE (tblDist.DID = [Forms]![frmDetail]![DID] AND tblDist.[Resolved?]=False AND tblDist.[Verified?]="UnVerified" AND tblDist.[Completed?]=False AND IIf(Left([Report Title],4)=Left([Forms]![frmDetail]![PNAME],4),True,False)=True)

OR
(tblDist.DID = [Forms]![frmDetail]![DID] AND IIf(Left[Brand Code],4) = "9999" AND tblDist.[Resolved?]=False AND tblDist.[Verified?]="UnVerified" AND tblDist.[Completed?]=False);

Get rid of all the LIKE's since it is highly inefficient and try the query again.

Are there indexes on the fields in the where clause?? If not try adding indexes one at a time and rerunning the query.