473,320 Members | 2,145 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

slow query in access and in vb datareport

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
1 1850
"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

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

11
by: DJJ | last post by:
I am using the MySQL ODBC 3.51 driver to link three relatively small MySQL tables to a Microsoft Access 2003 database. I am finding that the data from the MySQL tables takes a hell of a long time...
0
by: Cady Steldyn | last post by:
I need to run a data report that will query an Access_Table that has the following Project info: My Form has a command_button with the following code: Private Sub Command1_Click() Set db =...
3
by: Janross | last post by:
I'm having trouble with a query that's prohibitively slow. On my free-standing office computer it's fine (well, 2-4 seconds), but on the client's network, it takes at least 5 minutes to run. ...
12
by: VMI | last post by:
For some reason, the process of retrieving data (about 20 records) from an Access table that has 400K records to a dataTable is taking over 3 mins. to complete. Below is my code to connect to the...
0
by: roiavidan | last post by:
Hi, I'm having a bit of a problem with a small application I wrote in C#, which uses an Access database (mdb file) for storing financial data. After looking for a similiar topic and failing to...
6
by: klove1209 | last post by:
Good afternoon, Can someone please tell me how I can return records from a query. I have text fields that I need to obtain, and have those necessary records sent to MS Word. I need to return the...
3
by: suresh_punniyakkodi | last post by:
Hello to all, How to pass SQL Query to DataReport at Runtime... Please, Any body help for this Question... Regards, Suresh...
4
by: ashutoshvyas | last post by:
I am designing a data report in VB6. In data environment it is possible to selet tables and 'select quries'. But I want to generate a report based on parameters passed by user, so i had created a...
2
by: existential.philosophy | last post by:
This is a new problem for me: I have some queries that open very slowly in design view. My benchmark query takes about 20 minutes to open in design view. That same query takes about 20 minutes...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.