Re: Question about Queries with Subqueries

From http://office.microsoft.com/en-us/access/HA100307391033.aspx , the
indicated maximum number of levels for nested queries is 50 but in practice,
probably that you will be hit by resources limits or that Access will crash
on you well before you'ill reach this limit.

Also, I'm not sure but in your case, maybe it will be advantageous for
performance reasons to use UNION ALL instead of UNION.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"DawnTreader" <DawnTreader@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EB265527-6224-46EF-9ECE-06BA6B359499@xxxxxxxxxxxxxxxx
Hello All

i have a query i am working on that draws data from 3 different data
"paths". i am using a query with a union subquery to pull the information
i
need. it looks like this:

SELECT
SQ.ProdID,
SQ.Category,
SQ.IMWPNID,
SQ.PartDesc,
SQ.Ref,
SQ.PartBlockQTY,
SQ.ListPrice,
SQ.PLID,
nz([SQ].[PartBlockQTY],0)*nz([SQ].[ListPrice],0) AS SkidValue,
tblMasterPartList.[1000Maint] AS Maint1000Hr,
tblMasterPartList.[5000Rebuild] AS Rebuild5000Hr,
tblMasterPartList.[10000Rebuild] AS Rebuild10000Hr,
tblMasterPartList.[15000Rebuild] AS Rebuild15000Hr,
tblMasterPartList.[20000Rebuild] AS Rebuild20000Hr,
tblMasterPartList.[25000Rebuild] AS Rebuild25000Hr
FROM
[SELECT
tblProductPartList.ProductID AS ProdID,
subtblSectionName.SectionName AS Category,
tblProductPartList.IMWPartNumberID AS IMWPNID,
dbo_PART.DESCRIPTION AS PartDesc,
"" AS Ref,
tblProductPartList.QTY AS PartBlockQTY,
dbo_PART.UNIT_PRICE AS ListPrice,
tblProductPartList.PartListID AS PLID
FROM
subtblSectionName INNER JOIN (tblProductPartList LEFT JOIN dbo_PART
ON tblProductPartList.IMWPartNumberID = dbo_PART.ID)
ON subtblSectionName.SectionNameID = tblProductPartList.SectionNameID
WHERE
(((tblProductPartList.IMWPartNumberID) Is Not Null))

UNION SELECT
tblProductPartList.ProductID AS ProdID,
tblProductPartList.RequirementCategory AS Category,
dbo_REQUIREMENT.PART_ID AS IMWPNID,
dbo_PART.DESCRIPTION AS PartDesc,
dbo_REQUIREMENT.REFERENCE AS Ref,
dbo_REQUIREMENT.QTY_PER AS PartBlockQTY,
dbo_PART.UNIT_PRICE AS ListPrice,
tblProductPartList.PartListID AS PLID
FROM
(tblProductPartList LEFT JOIN dbo_REQUIREMENT
ON tblProductPartList.RequirementID = dbo_REQUIREMENT.ROWID)
LEFT JOIN dbo_PART ON dbo_REQUIREMENT.PART_ID = dbo_PART.ID
WHERE
(((tblProductPartList.RequirementID) Is Not Null))

UNION SELECT
tblProductList.ProductID AS ProdID,
"CBA" AS Category,
tblCBAPartList.IMWPartNumberID AS IMWPNID,
dbo_PART.DESCRIPTION AS PartDesc,
"CBA" AS Ref,
tblCBAPartList.QTY AS PartBlockQTY,
dbo_PART.UNIT_PRICE AS ListPrice,
CBAPartListID AS PLID
FROM
((tblCBBANumbers LEFT JOIN tblCBAPartList
ON tblCBBANumbers.CBBAID = tblCBAPartList.CBAID)
RIGHT JOIN (tblProductList LEFT JOIN subdatatblCompressor
ON (tblProductList.ProductTypeID = subdatatblCompressor.ProductTypeID)
AND (tblProductList.ProductID = subdatatblCompressor.ProductID))
ON tblCBBANumbers.CBBAID = subdatatblCompressor.CBAEMID)
LEFT JOIN dbo_PART ON tblCBAPartList.IMWPartNumberID = dbo_PART.ID
WHERE
(((tblProductPartList.IMWPartNumberID) Is Null)) AND
(((tblProductPartList.RequirementID) Is Null))]. AS SQ LEFT JOIN
tblMasterPartList ON SQ.IMWPNID = tblMasterPartList.ID;

i have a question about queries like this. first a little background

i have built this multiple times trying to find the most efficient way to
get the information in the format that i need. originally i had 7 levels
of
queries to get the result i am looking for. the problem is the 7th level
gets
so complex that it chokes on the "system resources exceeded" error and if
i
add another table it stops on "cannot open anymore databases". so i
decided
to try "flattening" my queries by using subqueries.

although the SQL above works there are 2 things i need to know before
going
further. the first is, how many subqueries can i do in SQL? the second
question, will i hit the same problem "Cannot open anymore databases" with
one query that is built with a few subqueries and subsubqueries?

unrelated, how do i get the third part of the union query to only show
those
parts not generated by the other 2 parts of the union query?


.

Relevant Pages

  • Re: Combining records from two queries
    ... combining UNION and SELECT queries to best effect. ... The list in each case has to have the same number & types of fields (for example, if the first Query begins with a Date/Time field, the second one should do so as well). ... I like to keep my Union Queries short and simple and do the rest of the work elsewhere. ... tblMentors comprises Subject Mentors and Professional Mentors and the Placement subform has a combo for each - the Subject Mentor combo puts the chosen MentorID in the SubjectMentorID field and the Professional Mentor combo puts the chosen MentorID in the ProfessionalMentorID field. ...
    (microsoft.public.access.queries)
  • Re: Combining records from two queries
    ... combining UNION and SELECT queries to best effect. ... Using my query names, can you say exactly what my SQL should be ... > my Union Queries short and simple and do the rest of the work elsewhere. ... >>> Subject and Professional Mentors that are allocated to placements. ...
    (microsoft.public.access.queries)
  • Question about Queries with Subqueries
    ... i have a query i am working on that draws data from 3 different data ... UNION SELECT ... to try "flattening" my queries by using subqueries. ...
    (microsoft.public.access.queries)
  • Re: Help! Union Query has started crashing!
    ... It's tblSupport on the RLR_SUPPORT_INFOTERRA.mdb database. ... I think I have tracked the problem down to the query ... I also tried a UNION ALL, ... the actual structure of the queries as they have been running fine for weeks. ...
    (microsoft.public.access.queries)
  • Re: Change of field name causes application to crash
    ... this all kind of got me searching through all the underlying queries ... have a six-way union query separated from the query in question by an ... Here is the six-way union query: ... I'm assuming that LookupInventoryCombinedNet is the UNION query you ...
    (microsoft.public.access.modulesdaovba)