How to evaluate a query in Access 2003

[SheepDog]

I have a query that runs extremely slow and want to know where the problem lies. If I go to tools-analyze-select the query I do not get any information back saying there is a problem.
Is there another way to check?

 

[BobJacksonNCI]

SheepDog,

Folks here can assist with the query design if you'll post the SQL...

Also, you could look up the tables being accessed, the specific fields, and then look at the table in design view to see if there would be a benefit to adding indexes.

If the query is run from a form and the form is loading slowly, turn off autocorrect.

HTH,
Labradog (Not really, but I DO have 5 great retrievers)

 

[SheepDog]

Here is the sql. It works fine last week and I have nto done anything to the query so I do not know why it is not working.

SELECT DISTINCT People_Information.*, dbo_Recruiter_Contact_Prospect_Select1.Recruiter_People_Code_ID, dbo_Recruiter_Contact_Prospect_Select1.Recruiter_FName, dbo_Recruiter_Contact_Prospect_Select1.Recruiter_LName, dbo_ACADEMIC.CLASS_LEVEL, dbo_ACADEMIC.APP_DECISION, dbo_SemesterInfo.academic_session AS Expr1
FROM dbo_SemesterInfo RIGHT JOIN ((dbo_Recruiter_Contact_Prospect_Select1 LEFT JOIN People_Information ON dbo_Recruiter_Contact_Prospect_Select1.PEOPLE_ID = People_Information.PEOPLE_ID) LEFT JOIN dbo_ACADEMIC ON dbo_Recruiter_Contact_Prospect_Select1.PEOPLE_ID = dbo_ACADEMIC.PEOPLE_ID) ON dbo_SemesterInfo.academic_year = dbo_ACADEMIC.ACADEMIC_YEAR
WHERE (((dbo_Recruiter_Contact_Prospect_Select1.Recruiter_People_Code_ID)=[Enter Recruiter People ID]) AND ((dbo_ACADEMIC.APP_DECISION)<>"cx" Or (dbo_ACADEMIC.APP_DECISION)="reg") AND ((dbo_Recruiter_Contact_Prospect_Select1.Select_Recruiter)=Yes) AND ((dbo_SemesterInfo.offset)=0));

 

[BobJacksonNCI]

It's important, no, it's CRITICAL to be as clear and concise (but comprehensive) as possible when describing the problem.

We now know:
1. It used to work as desired.
2. You haven't changed the query.

We don't know:
Your first post said the query is running "extremely slow".
Your second post says it is "not working".
To be helpful, we need to know whether it is running slowly and giving the expected result, or not running at all.

How is the query used? If the query is used with a form, a common form-related performance issue occurs (especially when using Access 2000) when Tools \ Options \ General \ "Perform name AutoCorrect" is checked.

If it's used with a form, make sure the autocorrect boxes are unchecked.

Run compact/repair.

Is the database on a server or your PC?



HTH,
Bob

 

[SheepDog]

If the query is used with a form, a common form-related performance issue occurs (especially when using Access 2000) when Tools \ Options \ General \ "Perform name AutoCorrect" is checked.

I have made sure it is unchecked!

If it's used with a form, make sure the autocorrect boxes are unchecked.

Run compact/repair.

I have run Compact/Repair

Is the database on a server or your PC?

DB is on Server

I just hit task manager to close out task since it never opens the report.

 

[BobJacksonNCI]

O.K.,

Does it run properly for another User?

If so, somehow permissions are involved.

Also, seems unusual that there is no message.
Is there a chance "DoCmd.SetWarnings False" has been executed (could be anywhere in the database's VBA code) without a subsequent "DoCmd.SetWarnings True" to turn warning messages back on?

Finally, have you tried to run the query by itself, outside the report? If so, was the result the same?

Bob