B
Bill R via AccessMonster.com
Here it is:
SELECT DMSID INTO tblFilteredDMSIDs
FROM qryRealDwgs
WHERE
Retire=0 AND [Current Location]<>21
AND DMSID NOT IN(
SELECT dmsid FROM [Location Map] INNER JOIN tblDates ON ([Location Map].
[Location Number] = tblDates.locid) AND ([Location Map].[Location Number] =
tblDates.locid) WHERE [Order]=21 AND Not Finish Is Null
)
AND DMSID IN(
SELECT dmsid FROM tblPhase INNER JOIN ([Location Map] INNER JOIN tblDates ON
([Location Map].[Location Number] = tblDates.locid) AND ([Location Map].
[Location Number] = tblDates.locid)) ON tblPhase.PHID = [Location Map].
phaseid WHERE PHID=3 AND Finish Is Null OR PHID=6 AND Finish Is Null
)
AND (Int([IPT_NO]) = 30 Or Int([IPT_NO]) = 40) AND DOC_TYPE = "ND"
OR
Retire=0 AND [Current Location]<>21
AND DMSID NOT IN(
SELECT dmsid FROM [Location Map] INNER JOIN tblDates ON ([Location Map].
[Location Number] = tblDates.locid) AND ([Location Map].[Location Number] =
tblDates.locid) WHERE [Order]=21 AND Not Finish Is Null
)
AND DMSID IN(
SELECT dmsid FROM tblPhase INNER JOIN ([Location Map] INNER JOIN tblDates ON
([Location Map].[Location Number] = tblDates.locid) AND ([Location Map].
[Location Number] = tblDates.locid)) ON tblPhase.PHID = [Location Map].
phaseid WHERE PHID=3 AND Finish Is Null OR PHID=6 AND Finish Is Null
)
AND (Int([IPT_NO]) = 30 Or Int([IPT_NO]) = 40) AND DOC_TYPE = "DDC"
The IN(SELECT queries look in tables that are represented by subforms in the
mainform, and the data is not in the recordsource of the mainform. This query
is the result of selections on a filter form. Depending on the other criteria
selected in the filter form, the rows of criteria (including the IN(SELECT
queries) can grow to 16 or more in number. The query parks the resultant
DMSIDs in a temp table so that once the job is done, the local temp table is
used as the filter in the mainform's recordsource from then on, until the
user decides to refilter the records.
It can be a painfully long wait while the query runs. Any suggestions?
Thanks,
BIll
SELECT DMSID INTO tblFilteredDMSIDs
FROM qryRealDwgs
WHERE
Retire=0 AND [Current Location]<>21
AND DMSID NOT IN(
SELECT dmsid FROM [Location Map] INNER JOIN tblDates ON ([Location Map].
[Location Number] = tblDates.locid) AND ([Location Map].[Location Number] =
tblDates.locid) WHERE [Order]=21 AND Not Finish Is Null
)
AND DMSID IN(
SELECT dmsid FROM tblPhase INNER JOIN ([Location Map] INNER JOIN tblDates ON
([Location Map].[Location Number] = tblDates.locid) AND ([Location Map].
[Location Number] = tblDates.locid)) ON tblPhase.PHID = [Location Map].
phaseid WHERE PHID=3 AND Finish Is Null OR PHID=6 AND Finish Is Null
)
AND (Int([IPT_NO]) = 30 Or Int([IPT_NO]) = 40) AND DOC_TYPE = "ND"
OR
Retire=0 AND [Current Location]<>21
AND DMSID NOT IN(
SELECT dmsid FROM [Location Map] INNER JOIN tblDates ON ([Location Map].
[Location Number] = tblDates.locid) AND ([Location Map].[Location Number] =
tblDates.locid) WHERE [Order]=21 AND Not Finish Is Null
)
AND DMSID IN(
SELECT dmsid FROM tblPhase INNER JOIN ([Location Map] INNER JOIN tblDates ON
([Location Map].[Location Number] = tblDates.locid) AND ([Location Map].
[Location Number] = tblDates.locid)) ON tblPhase.PHID = [Location Map].
phaseid WHERE PHID=3 AND Finish Is Null OR PHID=6 AND Finish Is Null
)
AND (Int([IPT_NO]) = 30 Or Int([IPT_NO]) = 40) AND DOC_TYPE = "DDC"
The IN(SELECT queries look in tables that are represented by subforms in the
mainform, and the data is not in the recordsource of the mainform. This query
is the result of selections on a filter form. Depending on the other criteria
selected in the filter form, the rows of criteria (including the IN(SELECT
queries) can grow to 16 or more in number. The query parks the resultant
DMSIDs in a temp table so that once the job is done, the local temp table is
used as the filter in the mainform's recordsource from then on, until the
user decides to refilter the records.
It can be a painfully long wait while the query runs. Any suggestions?
Thanks,
BIll