slow query in access and in vb datareport

Generale Cluster

Hello,
I need to extract the names of the employees which have not been active
during the last 3 months from the following tables

EMPLOYEES:
employee_id
name

COOPERATIVE
cooperative_id
company_name

HOURS
employee_id
customer_id
year_month <-- e.g. 06/2006 (Date/Time field)
regular_hours
nightly_hours

I need to know the names of the employees which either do not appear in the
HOURS table in the last three months or do appear but have 0 total hours
(regular+nightly) in the three months.
The following is the way I got the result, but it is definitely too slow: it
starts displaying the results in the query results, but it hangs when I
change record page.

SELECT distinct name, company_name from employees a, cooperative b
where a.cooperative_id=b.cooperative_id AND a.employee_id not in
(SELECT distinct hours.employee_id FROM hours where year_month>#06/2006#
group by employee_id HAVING sum(regular_hours+nightly_hours)=0)

Why is this query so slow? I have:
16000 rows in HOURS,
2000 rows in employees
10 rows in cooperative

How may I get the same result with a faster query ?
Thank you
Regards

Sep 30 '06 #1

Generale Cluster

"Generale Cluster" <al***************@unsampdorianosoftmasters.neth a
scritto nel messaggio news:ef**********@newsreader.mailgate.org...

Hello,
I need to extract the names of the employees which have not been active
during the last 3 months from the following tables

EMPLOYEES:
employee_id
name

COOPERATIVE
cooperative_id
company_name

HOURS
employee_id
customer_id
year_month <-- e.g. 06/2006 (Date/Time field)
regular_hours
nightly_hours

I need to know the names of the employees which either do not appear in

the

HOURS table in the last three months or do appear but have 0 total hours
(regular+nightly) in the three months.
The following is the way I got the result, but it is definitely too slow:

it

starts displaying the results in the query results, but it hangs when I
change record page.

Finally I got it:
I also had a mistake in the having condition, but I've found a faster
construct instead of NOT IN:

select a.employee_id, name from employyes a left join (SELECT distinct
hours.employee_id FROM hours where year_month>#06/2006#
group by employee_id HAVING sum(regular_hours+nightly_hours)<>0) AS b on
a.lavoratore_id=b.lavoratore_id WHERE b.lavoratore_id is null order by name

This is extremely faster.
Regards

Sep 30 '06 #2