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 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
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
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;
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;
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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
...
|
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...
|
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)...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
| | |