Very complex query

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: "Bill R via AccessMonster.com" <u9289@uwe>
Date: Thu, 13 Oct 2005 13:02:53 GMT

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

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200510/1
.

Follow-Ups:
- Re: Very complex query
  - From: Rob Oldfield

Prev by Date: Re: access group by aand count
Next by Date: Re: Integrating the code for a max.date function with a check box controlling it and
Previous by thread: Re: access group by aand count
Next by thread: Re: Very complex query
Index(es):
- Date
- Thread

Relevant Pages

Re: Very complex query
... run multiple times: twice in the example you give, ... AND DMSID NOT IN( ... SELECT dmsid FROM tblPhase INNER JOIN (INNER JOIN tblDates ON ... > the drawings list for those drawings that are open in a specific phase of ...
(microsoft.public.access.queries)
Re: Very complex query
... Will it always be the case that the select dmsid inand dmsid not ... > SELECT dmsid FROM tblPhase INNER JOIN (INNER JOIN tblDates ... > selected in the filter form, the rows of criteria (including the IN(SELECT ... The query parks the resultant ...
(microsoft.public.access.queries)