473,322 Members | 1,526 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,322 software developers and data experts.

Help with Select, Watch out this is a hard one.

Table Structure.

tblData
----------
fldID (AutoNumber) (PK)
fldType (String)
fldLocationID (Number)

tblLocation
---------------
fldID (AutoNumber) (PK)
fldDescription (String)

FKConstraint (tblData.fldLocationID = tblLocation.fldID)

So tblData.fldType is a String that holds the Types of Data, EX =
"OldData", "NewData", "OtherData".
This is used to organize the data by type (Not smart, but I inherited
project can't change now)

So The results I am looking for is to get all "OldData" from any
location where the only type of data for that Location is "OldData".

Here is a select that works, BUT IT IS WAY TO SLOW, since there are
over 30000 records. and over 9000 locations.

SELECT *
FROM tblData
WHERE tblData.fldType = "OldData"
and fldLocationID NOT IN
(SELECT tblData.fldLocationID
FROM tblData
WHERE tblData.fldType < "OldData")

Here is the sample data I have used.

tblLocation
----------------
fldID fldDescription
0 Home
1 Work
2 Unknown

tblData
----------
fldID fldType fldLocationID
10 OldData 0
20 OldData 1
30 NewData 0
40 NewData 2
50 OtherData 0
60 OtherData 2

Proper Results
---------------------
20,OldData,1

Anyone with any ideas would be grateful.

Jordon

Jan 8 '07 #1
4 1386

jo*********@gmail.com wrote:
Table Structure.

tblData
----------
fldID (AutoNumber) (PK)
fldType (String)
fldLocationID (Number)

tblLocation
---------------
fldID (AutoNumber) (PK)
fldDescription (String)

FKConstraint (tblData.fldLocationID = tblLocation.fldID)

So tblData.fldType is a String that holds the Types of Data, EX =
"OldData", "NewData", "OtherData".
This is used to organize the data by type (Not smart, but I inherited
project can't change now)

So The results I am looking for is to get all "OldData" from any
location where the only type of data for that Location is "OldData".

Here is a select that works, BUT IT IS WAY TO SLOW, since there are
over 30000 records. and over 9000 locations.

SELECT *
FROM tblData
WHERE tblData.fldType = "OldData"
and fldLocationID NOT IN
(SELECT tblData.fldLocationID
FROM tblData
WHERE tblData.fldType < "OldData")
Your approach is the right one, although adding a DISTINCT clause to
the subquery, i.e.,

SELECT *
FROM tblData
WHERE tblData.fldType = "OldData"
and fldLocationID NOT IN
(SELECT DISTINCT tblData.fldLocationID
FROM tblData
WHERE tblData.fldType < "OldData")

might be slightly more efficient. Do you have indexes defined for the
[fldLoactionID] and [fldType] columns?

Here is the sample data I have used.

tblLocation
----------------
fldID fldDescription
0 Home
1 Work
2 Unknown

tblData
----------
fldID fldType fldLocationID
10 OldData 0
20 OldData 1
30 NewData 0
40 NewData 2
50 OtherData 0
60 OtherData 2

Proper Results
---------------------
20,OldData,1

Anyone with any ideas would be grateful.

Jordon
Jan 8 '07 #2
It occurred to me after posting that having

tblData.fldType = "OldData"

in the WHERE clause is redundant given that the subquery already
filters out location IDs that have any other value. So, the following
should work:

SELECT *
FROM tblData
WHERE fldLocationID NOT IN
(SELECT DISTINCT tblData.fldLocationID
FROM tblData
WHERE tblData.fldType < "OldData")

You still should have indexes on the [fldLocationID] and [fldType]
columns.
Gord wrote:
jo*********@gmail.com wrote:
Table Structure.

tblData
----------
fldID (AutoNumber) (PK)
fldType (String)
fldLocationID (Number)

tblLocation
---------------
fldID (AutoNumber) (PK)
fldDescription (String)

FKConstraint (tblData.fldLocationID = tblLocation.fldID)

So tblData.fldType is a String that holds the Types of Data, EX =
"OldData", "NewData", "OtherData".
This is used to organize the data by type (Not smart, but I inherited
project can't change now)

So The results I am looking for is to get all "OldData" from any
location where the only type of data for that Location is "OldData".

Here is a select that works, BUT IT IS WAY TO SLOW, since there are
over 30000 records. and over 9000 locations.

SELECT *
FROM tblData
WHERE tblData.fldType = "OldData"
and fldLocationID NOT IN
(SELECT tblData.fldLocationID
FROM tblData
WHERE tblData.fldType < "OldData")

Your approach is the right one, although adding a DISTINCT clause to
the subquery, i.e.,

SELECT *
FROM tblData
WHERE tblData.fldType = "OldData"
and fldLocationID NOT IN
(SELECT DISTINCT tblData.fldLocationID
FROM tblData
WHERE tblData.fldType < "OldData")

might be slightly more efficient. Do you have indexes defined for the
[fldLoactionID] and [fldType] columns?

Here is the sample data I have used.

tblLocation
----------------
fldID fldDescription
0 Home
1 Work
2 Unknown

tblData
----------
fldID fldType fldLocationID
10 OldData 0
20 OldData 1
30 NewData 0
40 NewData 2
50 OtherData 0
60 OtherData 2

Proper Results
---------------------
20,OldData,1

Anyone with any ideas would be grateful.

Jordon
Jan 9 '07 #3
jo*********@gmail.com wrote:

NOT IN almost always means

"The query will finish but NOT IN your life time."

I would try a Left Join.

SELECT td.*
FROM tblData td
LEFT JOIN
[SELECT fldLocationID FROM tblData WHERE fldType<>"OldData"]. sq
ON td.fldLocationID=sq.fldLocationID
WHERE td.fldType="OldData"
AND sq.fldLocationID IS NULL;

Jan 9 '07 #4
To all,

Sorry for the late reply, I was having problems replying.

Thank you for all your replies. The fldType was not indexed. Once
indexing the field the results were returned very fast. I cannot
believe the system was in place for over 3 years without indexing that
field. (silly me for assuming).

I stayed with my first Select even thought the join would work better,
I needed to add the filter as a where clause only and keep the existing
Joins in place. since it is used all over the program :(.

I did use the distinct though thank you, I would hvae never thought of
it. Even though it is selecting the Primary Key field so distinct
shouldn't make a diference, but I am no expert on MS-Access.

Thank you again,
Jordon

On Jan 9, 6:06 am, "Lyle Fairfield" <lylefairfi...@aim.comwrote:
jordonkr...@gmail.com wrote:NOT IN almost always means

"The query will finish but NOT IN your life time."

I would try a Left Join.

SELECT td.*
FROM tblData td
LEFT JOIN
[SELECT fldLocationID FROM tblData WHERE fldType<>"OldData"]. sq
ON td.fldLocationID=sq.fldLocationID
WHERE td.fldType="OldData"
AND sq.fldLocationID IS NULL;
Jan 16 '07 #5

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

Similar topics

0
by: abcd | last post by:
kutthaense Secretary Djetvedehald H. Rumsfeld legai predicted eventual vicmadhlary in Iraq mariyu Afghmadhlaistmadhla, kaani jetvedehly after "a ljetvedehg, hard slog," mariyu vede legai pressed...
5
by: Craig Keightley | last post by:
Please help, i have attached my page which worksin IE but i cannnot get the drop down menu to fucntion in firefox. Any one have any ideas why? Many Thanks Craig ...
17
by: MLH | last post by:
After running the following code snippet... MyURL = "http://tycho.usno.navy.mil/what.html" msXML.Open "GET", MyURL, False msXML.send I would like to execute code to perform essentially what...
2
by: James | last post by:
i wrote a filewatcher application and inside the code, i've a select case statement : Private Sub FileSystemWatcher1_Created(ByVal sender As Object, ByVal e As System.IO.FileSystemEventArgs)...
8
by: Dip | last post by:
Hello Experts, Here is the code to flatten a PC hierarchy into a level based table. It works fine. SELECT t1.TASK_ID AS TASK_LV1, t2.TASK_ID AS TASK_LV2, t3.TASK_ID AS TASK_LV3, t4.TASK_ID AS...
15
by: Jay | last post by:
I have a multi threaded VB.NET application (4 threads) that I use to send text messages to many, many employees via system.timer at a 5 second interval. Basically, I look in a SQL table (queue) to...
5
by: turtle | last post by:
Background: I have a table of fields each field representing a month (Jan, Feb, Mar...). These are not a date field but more of a bucket to hold labor actuals. Along with the months their is...
11
by: troy_lee | last post by:
I have two fields on a form. These two fields' values are based on an expression and represent a date range. I need to create a SQL statement that will use the returned values of these two fields...
6
by: troy_lee | last post by:
I am trying to count the total number of units for a given part number that have a Priority rating of 1. Based upon some research, this is what I came up with for my query. Access says that I have...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
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: 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: 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...

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.