unique records causing Access to loop

[psimon88]

I am running a report in Access 2003 off of DB2. I am pulling multiple records for an event and am using 'select unique' to pare them down.

Unfortunately, when I do this, it causes Access to loop. My time-out setting is 600 seconds and the data set isn't that large (2,000 records).

Anyone know why this is happening and what I can do to fix this?

Thanks in advance.

I run this using values from a form w/o any VB code.

SQL below:

SELECT DISTINCT qry_EMPLOYEE_FIELDTERMS4.COMPANY, qry_EMPLOYEE_FIELDTERMS4.STORE, qry_EMPLOYEE_FIELDTERMS4.EMPLOYEE, qry_EMPLOYEE_FIELDTERMS4.LAST_NAME, qry_EMPLOYEE_FIELDTERMS4.FIRST_NAME, qry_EMPLOYEE_FIELDTERMS4.EMP_STATUS, qry_EMPLOYEE_FIELDTERMS4.DEPARTMENT, qry_EMPLOYEE_FIELDTERMS4.[DEPT NAME], qry_EMPLOYEE_FIELDTERMS4.JOB_CODE, qry_EMPLOYEE_FIELDTERMS4.[JOB TITLE], qry_EMPLOYEE_FIELDTERMS4.[JOB CL], qry_EMPLOYEE_FIELDTERMS4.PAY_RATE, qry_EMPLOYEE_FIELDTERMS4.[ADJ HIRE], qry_EMPLOYEE_FIELDTERMS4.[Store Region], qry_EMPLOYEE_FIELDTERMS4.[Store District], qry_EMPLOYEE_FIELDTERMS4.TERM_DATE, qry_EMPLOYEE_FIELDTERMS4.TERMMONTH, qry_EMPLOYEE_FIELDTERMS4.TERMYEAR, LAWDB2_PERSACTHST.ACTION_CODE, LAWDB2_PERSACTHST.EFFECT_DATE, LAWDB2_PERSACTHST.REASON_01, qry_EMPLOYEE_FIELDTERMS4.EEO_CLASS, qry_EMPLOYEE_FIELDTERMS4.SEX, qry_EMPLOYEE_FIELDTERMS4.BIRTHDATE, qry_EMPLOYEE_FIELDTERMS4.ANNIVERS_DATE
FROM qry_EMPLOYEE_FIELDTERMS4 LEFT JOIN LAWDB2_PERSACTHST ON (qry_EMPLOYEE_FIELDTERMS4.EMPLOYEE = LAWDB2_PERSACTHST.EMPLOYEE) AND (qry_EMPLOYEE_FIELDTERMS4.COMPANY = LAWDB2_PERSACTHST.COMPANY)
WHERE (((LAWDB2_PERSACTHST.ACTION_CODE)="TERM") AND ((LAWDB2_PERSACTHST.REASON_01) Like "TER*") AND ((Month([EFFECT_DATE]))=[Forms]![frm_intro]![month]) AND ((Year([EFFECT_DATE]))=[Forms]![frm_intro]![year]));

someone suggested that I take off the TER*. I did but it still took forever.

Thanks in advance.

ps

 

[dhookom]

What actually takes the time, the query or the report? Are all the fields used in joins, sorting, and criteria indexed?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Golom]

I infer that [blue]qry_EMPLOYEE_FIELDTERMS4[/blue] is a query rather than a table. How long does that query need to run when you run it stand-alone?

You might try doing a MAKE TABLE from [blue]qry_EMPLOYEE_FIELDTERMS4[/blue] and then run this query using that table rather than having it dynamically run the query.