Remove Duplicates in a Union Query

D

DawnTreader

here is my SQL as it stands:

SELECT ALL
ProductID,
WO,
Section,
IMWPNID,
PartDesc,
Manufacturer,
MfgPN,
REF,
BQTY,
SQTY,
UnitPrice,
SkidValue,
GoodFilter,
[1000HrMaint],
[5000HrRebuild],
[10000HrRebuild],
[15000HrRebuild],
[20000HrRebuild],
[25000HrRebuild],
PartListId
FROM aqryNewPartListIdea
UNION ALL SELECT
ProductID,
WO,
"" AS Section,
IMWPNID,
PartDesc,
Manufacturer,
MfgPN,
"" AS REF,
BQTY,
SQTY,
UValue AS UnitPrice,
SValue AS SkidValue,
GoodFilter,
[1000HrMaint],
[5000HrRebuild],
[10000HrRebuild],
[15000HrRebuild],
[20000HrRebuild],
[25000HrRebuild],
"From aqryTestingCBAPartList" AS PartListId
FROM aqryTestingCBAPartList
ORDER BY IMWPNID;

how do i tell it not to show a record from the second query if the IMWPNID
is the same as the first query? i found some SQL someone posted but it
crapped out this was the line i added based on the SQL i found:

WHERE aqryTestingCBAPartList.IMWPNID <> (SELECT DISTINCT
aqryNewPartListIdea.IMWPNID FROM aqryNewPartListIdea)

when i ran it i got the message:

"At most one record can be returned by this subquery. (Error 3354)"

i changed a few things and it gave me results, and then when i scrolled down
the records it crapped out again and gave me the same message, multiple
times, then showed ?#Name in all the fields in all the columns.

anyone have a suggestion?
 
D

DawnTreader

Hello Karl

thanks for the response. :)

SELECT ALL
ProductID,
WO,
Section,
IMWPNID,
PartDesc,
Manufacturer,
MfgPN,
REF,
BQTY,
SQTY,
UnitPrice,
SkidValue,
GoodFilter,
[1000HrMaint],
[5000HrRebuild],
[10000HrRebuild],
[15000HrRebuild],
[20000HrRebuild],
[25000HrRebuild],
PartListId
FROM aqryNewPartListIdea
UNION SELECT
ProductID,
WO,
"" AS Section,
IMWPNID,
PartDesc,
Manufacturer,
MfgPN,
"" AS REF,
BQTY,
SQTY,
UValue AS UnitPrice,
SValue AS SkidValue,
GoodFilter,
[1000HrMaint],
[5000HrRebuild],
[10000HrRebuild],
[15000HrRebuild],
[20000HrRebuild],
[25000HrRebuild],
"From aqryTestingCBAPartList" AS PartListId
FROM aqryTestingCBAPartList
WHERE aqryTestingCBAPartList.IMWPNID <> (SELECT aqryNewPartListIdea.IMWPNID
FROM aqryNewPartListIdea)
ORDER BY IMWPNID;

Still gives the same error. :(

KARL DEWEY said:
Drop the 'ALL' form 'UNION ALL SELECT.'
--
KARL DEWEY
Build a little - Test a little


DawnTreader said:
here is my SQL as it stands:

SELECT ALL
ProductID,
WO,
Section,
IMWPNID,
PartDesc,
Manufacturer,
MfgPN,
REF,
BQTY,
SQTY,
UnitPrice,
SkidValue,
GoodFilter,
[1000HrMaint],
[5000HrRebuild],
[10000HrRebuild],
[15000HrRebuild],
[20000HrRebuild],
[25000HrRebuild],
PartListId
FROM aqryNewPartListIdea
UNION ALL SELECT
ProductID,
WO,
"" AS Section,
IMWPNID,
PartDesc,
Manufacturer,
MfgPN,
"" AS REF,
BQTY,
SQTY,
UValue AS UnitPrice,
SValue AS SkidValue,
GoodFilter,
[1000HrMaint],
[5000HrRebuild],
[10000HrRebuild],
[15000HrRebuild],
[20000HrRebuild],
[25000HrRebuild],
"From aqryTestingCBAPartList" AS PartListId
FROM aqryTestingCBAPartList
ORDER BY IMWPNID;

how do i tell it not to show a record from the second query if the IMWPNID
is the same as the first query? i found some SQL someone posted but it
crapped out this was the line i added based on the SQL i found:

WHERE aqryTestingCBAPartList.IMWPNID <> (SELECT DISTINCT
aqryNewPartListIdea.IMWPNID FROM aqryNewPartListIdea)

when i ran it i got the message:

"At most one record can be returned by this subquery. (Error 3354)"

i changed a few things and it gave me results, and then when i scrolled down
the records it crapped out again and gave me the same message, multiple
times, then showed ?#Name in all the fields in all the columns.

anyone have a suggestion?
Click to expand...
Click to expand...
 
J

John W. Vinson

how do i tell it not to show a record from the second query if the IMWPNID
is the same as the first query? i found some SQL someone posted but it
crapped out this was the line i added based on the SQL i found:
Click to expand...

The UNION (without UNION ALL) will exclude only those records which are exact
duplicates in all fields. I think you're looking for an "unmatched query" for
the second half of the UNION.

The SELECT ALL in the first query is suspicious, unless you (very unwisely)
have a field named ALL in your table. Try:

SELECT
ProductID,
WO,
Section,
IMWPNID,
PartDesc,
Manufacturer,
MfgPN,
REF,
BQTY,
SQTY,
UnitPrice,
SkidValue,
GoodFilter,
[1000HrMaint],
[5000HrRebuild],
[10000HrRebuild],
[15000HrRebuild],
[20000HrRebuild],
[25000HrRebuild],
PartListId
FROM aqryNewPartListIdea
UNION
SELECT
ProductID,
WO,
"" AS Section,
IMWPNID,
PartDesc,
Manufacturer,
MfgPN,
"" AS REF,
BQTY,
SQTY,
UValue AS UnitPrice,
SValue AS SkidValue,
GoodFilter,
[1000HrMaint],
[5000HrRebuild],
[10000HrRebuild],
[15000HrRebuild],
[20000HrRebuild],
[25000HrRebuild],
"From aqryTestingCBAPartList" AS PartListId
FROM aqryTestingCBAPartList LEFT JOIN aqryNewPartListIdea
ON aqryTestingCBAPartList.IMWPNID = aqryNewPartListIdea.IMWPNID
WHERE aqryNewPartListIdea.IMWPNID IS NULL
ORDER BY IMWPNID;


You may need to specify tablenames in the second query since I'm suggesting
basing it on a JOIN.
 
D

DawnTreader

Hello John

Actually the situation is this, the aqryNewPartListIdea has parts that may
also be in aqryTestingCBAPartList. i want to show everything from the first
query and only those in the second query that dont match something already
listed. for instance if part # 321123 is listed from the first query, and it
exists in the second query only show it from the first query.

i dont want duplicates created by the second set of data produced by
aqryTestingCBAPartList.

John W. Vinson said:
how do i tell it not to show a record from the second query if the IMWPNID
is the same as the first query? i found some SQL someone posted but it
crapped out this was the line i added based on the SQL i found:
Click to expand...

The UNION (without UNION ALL) will exclude only those records which are exact
duplicates in all fields. I think you're looking for an "unmatched query" for
the second half of the UNION.

The SELECT ALL in the first query is suspicious, unless you (very unwisely)
have a field named ALL in your table. Try:

SELECT
ProductID,
WO,
Section,
IMWPNID,
PartDesc,
Manufacturer,
MfgPN,
REF,
BQTY,
SQTY,
UnitPrice,
SkidValue,
GoodFilter,
[1000HrMaint],
[5000HrRebuild],
[10000HrRebuild],
[15000HrRebuild],
[20000HrRebuild],
[25000HrRebuild],
PartListId
FROM aqryNewPartListIdea
UNION
SELECT
ProductID,
WO,
"" AS Section,
IMWPNID,
PartDesc,
Manufacturer,
MfgPN,
"" AS REF,
BQTY,
SQTY,
UValue AS UnitPrice,
SValue AS SkidValue,
GoodFilter,
[1000HrMaint],
[5000HrRebuild],
[10000HrRebuild],
[15000HrRebuild],
[20000HrRebuild],
[25000HrRebuild],
"From aqryTestingCBAPartList" AS PartListId
FROM aqryTestingCBAPartList LEFT JOIN aqryNewPartListIdea
ON aqryTestingCBAPartList.IMWPNID = aqryNewPartListIdea.IMWPNID
WHERE aqryNewPartListIdea.IMWPNID IS NULL
ORDER BY IMWPNID;


You may need to specify tablenames in the second query since I'm suggesting
basing it on a JOIN.
Click to expand...
 
K

KARL DEWEY

I did not see the first 'ALL' but it needs to be removed also.

--
KARL DEWEY
Build a little - Test a little


DawnTreader said:
Hello Karl

thanks for the response. :)

SELECT ALL
ProductID,
WO,
Section,
IMWPNID,
PartDesc,
Manufacturer,
MfgPN,
REF,
BQTY,
SQTY,
UnitPrice,
SkidValue,
GoodFilter,
[1000HrMaint],
[5000HrRebuild],
[10000HrRebuild],
[15000HrRebuild],
[20000HrRebuild],
[25000HrRebuild],
PartListId
FROM aqryNewPartListIdea
UNION SELECT
ProductID,
WO,
"" AS Section,
IMWPNID,
PartDesc,
Manufacturer,
MfgPN,
"" AS REF,
BQTY,
SQTY,
UValue AS UnitPrice,
SValue AS SkidValue,
GoodFilter,
[1000HrMaint],
[5000HrRebuild],
[10000HrRebuild],
[15000HrRebuild],
[20000HrRebuild],
[25000HrRebuild],
"From aqryTestingCBAPartList" AS PartListId
FROM aqryTestingCBAPartList
WHERE aqryTestingCBAPartList.IMWPNID <> (SELECT aqryNewPartListIdea.IMWPNID
FROM aqryNewPartListIdea)
ORDER BY IMWPNID;

Still gives the same error. :(

KARL DEWEY said:
Drop the 'ALL' form 'UNION ALL SELECT.'
--
KARL DEWEY
Build a little - Test a little


DawnTreader said:
here is my SQL as it stands:

SELECT ALL
ProductID,
WO,
Section,
IMWPNID,
PartDesc,
Manufacturer,
MfgPN,
REF,
BQTY,
SQTY,
UnitPrice,
SkidValue,
GoodFilter,
[1000HrMaint],
[5000HrRebuild],
[10000HrRebuild],
[15000HrRebuild],
[20000HrRebuild],
[25000HrRebuild],
PartListId
FROM aqryNewPartListIdea
UNION ALL SELECT
ProductID,
WO,
"" AS Section,
IMWPNID,
PartDesc,
Manufacturer,
MfgPN,
"" AS REF,
BQTY,
SQTY,
UValue AS UnitPrice,
SValue AS SkidValue,
GoodFilter,
[1000HrMaint],
[5000HrRebuild],
[10000HrRebuild],
[15000HrRebuild],
[20000HrRebuild],
[25000HrRebuild],
"From aqryTestingCBAPartList" AS PartListId
FROM aqryTestingCBAPartList
ORDER BY IMWPNID;

how do i tell it not to show a record from the second query if the IMWPNID
is the same as the first query? i found some SQL someone posted but it
crapped out this was the line i added based on the SQL i found:

WHERE aqryTestingCBAPartList.IMWPNID <> (SELECT DISTINCT
aqryNewPartListIdea.IMWPNID FROM aqryNewPartListIdea)

when i ran it i got the message:

"At most one record can be returned by this subquery. (Error 3354)"

i changed a few things and it gave me results, and then when i scrolled down
the records it crapped out again and gave me the same message, multiple
times, then showed ?#Name in all the fields in all the columns.

anyone have a suggestion?
Click to expand...
Click to expand...
Click to expand...
 
J

John W. Vinson

Hello John

Actually the situation is this, the aqryNewPartListIdea has parts that may
also be in aqryTestingCBAPartList. i want to show everything from the first
query and only those in the second query that dont match something already
listed. for instance if part # 321123 is listed from the first query, and it
exists in the second query only show it from the first query.

i dont want duplicates created by the second set of data produced by
aqryTestingCBAPartList.
Click to expand...

I understand. And that is precisely what my query is intended to accomplish,
by using a "frustrated outer join" unmatched query to exclude all
aqryNewPartListIdea records from consideration.

Back up your database and try it.
 
D

DawnTreader

Hello John

thats the one!

i was leary about it because the is null is on a field that shouldnt be
null. even if it matches one record in the first query. i wish i could see
this in a query diagram, but because it is a union i cant. :(

i understand things better when i can see the joins. how does this sql work?
 
D

DawnTreader

Hello again

i just cut it in half and tried the second part of the query.

then i looked at in qbe mode. i understand now.
 
D

DawnTreader

Hello all

ok. so now that i have that working, i have a question related to this.

i have 2 queries that i am using to make this union query. here is the
working sql as it stands:

SELECT
ProductID,
WO,
Section,
IMWPNID,
PartDesc,
Manufacturer,
MfgPN,
REF,
BQTY,
SQTY,
UnitPrice,
SkidValue,
GoodFilter,
[1000HrMaint],
[5000HrRebuild],
[10000HrRebuild],
[15000HrRebuild],
[20000HrRebuild],
[25000HrRebuild],
PartListId
FROM aqryNewPartListIdea
UNION SELECT
aqryTestingCBAPartList.ProductID,
aqryTestingCBAPartList.WO,
"" AS Section,
aqryTestingCBAPartList.IMWPNID,
aqryTestingCBAPartList.PartDesc,
aqryTestingCBAPartList.Manufacturer,
aqryTestingCBAPartList.MfgPN,
"" AS REF,
aqryTestingCBAPartList.BQTY,
aqryTestingCBAPartList.SQTY,
aqryTestingCBAPartList.UValue AS UnitPrice,
aqryTestingCBAPartList.SValue AS SkidValue,
aqryTestingCBAPartList.GoodFilter,
aqryTestingCBAPartList.[1000HrMaint],
aqryTestingCBAPartList.[5000HrRebuild],
aqryTestingCBAPartList.[10000HrRebuild],
aqryTestingCBAPartList.[15000HrRebuild],
aqryTestingCBAPartList.[20000HrRebuild],
aqryTestingCBAPartList.[25000HrRebuild],
"From aqryTestingCBAPartList" AS PartListId
FROM aqryTestingCBAPartList LEFT JOIN aqryNewPartListIdea ON
aqryTestingCBAPartList.IMWPNID = aqryNewPartListIdea.IMWPNID
WHERE aqryNewPartListIdea.IMWPNID IS NULL
ORDER BY IMWPNID;

i am trying to make things really efficient in the app i am working on and i
am wondering if it is more efficient to do my queries aqryTestingCBAPartList
and aqryNewPartListIdea, combine the results in this union query and then
query the results and put my filters for my form in that query, or can i
bring the sql from my seperate queries into the union and then query it to
put in my form filters?

basically what is more efficient? a string of levels of queries, or putting
everything in to as few queries as possible? for that matter can i use form
fields as criteria in my union query and take off another level?

i know that a union query with everything i need in it would be complicated,
but i am up to the challenge. :)

DawnTreader said:
here is my SQL as it stands:

SELECT ALL
ProductID,
WO,
Section,
IMWPNID,
PartDesc,
Manufacturer,
MfgPN,
REF,
BQTY,
SQTY,
UnitPrice,
SkidValue,
GoodFilter,
[1000HrMaint],
[5000HrRebuild],
[10000HrRebuild],
[15000HrRebuild],
[20000HrRebuild],
[25000HrRebuild],
PartListId
FROM aqryNewPartListIdea
UNION ALL SELECT
ProductID,
WO,
"" AS Section,
IMWPNID,
PartDesc,
Manufacturer,
MfgPN,
"" AS REF,
BQTY,
SQTY,
UValue AS UnitPrice,
SValue AS SkidValue,
GoodFilter,
[1000HrMaint],
[5000HrRebuild],
[10000HrRebuild],
[15000HrRebuild],
[20000HrRebuild],
[25000HrRebuild],
"From aqryTestingCBAPartList" AS PartListId
FROM aqryTestingCBAPartList
ORDER BY IMWPNID;

how do i tell it not to show a record from the second query if the IMWPNID
is the same as the first query? i found some SQL someone posted but it
crapped out this was the line i added based on the SQL i found:

WHERE aqryTestingCBAPartList.IMWPNID <> (SELECT DISTINCT
aqryNewPartListIdea.IMWPNID FROM aqryNewPartListIdea)

when i ran it i got the message:

"At most one record can be returned by this subquery. (Error 3354)"

i changed a few things and it gave me results, and then when i scrolled down
the records it crapped out again and gave me the same message, multiple
times, then showed ?#Name in all the fields in all the columns.

anyone have a suggestion?
Click to expand...
 
J

John W. Vinson

i was leary about it because the is null is on a field that shouldnt be
null. even if it matches one record in the first query. i wish i could see
this in a query diagram, but because it is a union i cant. :(
Click to expand...

The field will be NULL if there is no matching record in the second table in
the query. Glad you got it figured out!
 
M

Michel Walsh

(...)
The SELECT ALL in the first query is suspicious
Click to expand...

(...)


In theory the first ALL is what we get by default and is to be compared to
the other available 'option': DISTINCT. ie, it is either ALL either
DISTINCT, and by default, it is ALL.

Even if someone would be using SELECT ALL * FROM ... UNION SELECT ALL *
FROM ...
there should be no dup because the UNION does not, itself, uses ALL., as
already pointed out. Someone can try, with Northwind


SELECT ALL OrderID FROM [Order Details]
UNION
SELECT 0 FROM [Order Details]


and indeed, it removes the duplicated orderID (and add a record with zero)





Vanderghast, Access MVP
 
D

DawnTreader

Hello again

ok. new take on this.

how would i do a 3 union from clause to get the same ""frustrated outer
join" unmatched query" that we did with the 2.

here is the intended SQL:

SELECT
qryrptSerialManualPartList5000HrRebuild.ProductID,
qryrptSerialManualPartList5000HrRebuild.WO,
qryrptSerialManualPartList5000HrRebuild.ProductSection,
qryrptSerialManualPartList5000HrRebuild.IMWPNID,
qryrptSerialManualPartList5000HrRebuild.PartDesc,
qryrptSerialManualPartList5000HrRebuild.Manufacturer,
qryrptSerialManualPartList5000HrRebuild.MfgPN,
qryrptSerialManualPartList5000HrRebuild.REF,
qryrptSerialManualPartList5000HrRebuild.PartBlockQTY,
qryrptSerialManualPartList5000HrRebuild.PartSkidQTY,
qryrptSerialManualPartList5000HrRebuild.UnitPrice,
qryrptSerialManualPartList5000HrRebuild.SkidValue,
qryrptSerialManualPartList5000HrRebuild.GoodFilter,
qryrptSerialManualPartList5000HrRebuild.Rebuild,
qryrptSerialManualPartList5000HrRebuild.PartListId
FROM qryrptSerialManualPartList5000HrRebuild
UNION ALL
SELECT
qryrptSerialReqPartList5000HrRebuild.ProductID,
qryrptSerialReqPartList5000HrRebuild.WO,
qryrptSerialReqPartList5000HrRebuild.ProductSection,
qryrptSerialReqPartList5000HrRebuild.IMWPNID,
qryrptSerialReqPartList5000HrRebuild.PartDesc,
qryrptSerialReqPartList5000HrRebuild.Manufacturer,
qryrptSerialReqPartList5000HrRebuild.MfgPN,
qryrptSerialReqPartList5000HrRebuild.REF,
qryrptSerialReqPartList5000HrRebuild.PartBlockQTY,
qryrptSerialReqPartList5000HrRebuild.PartSkidQTY,
qryrptSerialReqPartList5000HrRebuild.UnitPrice,
qryrptSerialReqPartList5000HrRebuild.SkidValue,
qryrptSerialReqPartList5000HrRebuild.GoodFilter,
qryrptSerialReqPartList5000HrRebuild.Rebuild,
qryrptSerialReqPartList5000HrRebuild.PartListId
FROM qryrptSerialReqPartList5000HrRebuild
UNION SELECT
qryrptCBARebuildPartList5000HrRebuild.ProductID,
qryrptCBARebuildPartList5000HrRebuild.WO,
qryrptCBARebuildPartList5000HrRebuild.ProductSection,
qryrptCBARebuildPartList5000HrRebuild.IMWPNID,
qryrptCBARebuildPartList5000HrRebuild.PartDesc,
qryrptCBARebuildPartList5000HrRebuild.Manufacturer,
qryrptCBARebuildPartList5000HrRebuild.MfgPN,
qryrptCBARebuildPartList5000HrRebuild.REF,
qryrptCBARebuildPartList5000HrRebuild.PartBlockQTY,
qryrptCBARebuildPartList5000HrRebuild.PartSkidQTY,
qryrptCBARebuildPartList5000HrRebuild.UnitPrice,
qryrptCBARebuildPartList5000HrRebuild.SkidValue,
qryrptCBARebuildPartList5000HrRebuild.GoodFilter,
qryrptCBARebuildPartList5000HrRebuild.Rebuild,
qryrptCBARebuildPartList5000HrRebuild.PartListId
FROM
(qryrptCBARebuildPartList5000HrRebuild
LEFT JOIN
qryrptSerialReqPartList5000HrRebuild
ON
qryrptCBARebuildPartList5000HrRebuild.IMWPNID =
qryrptSerialReqPartList5000HrRebuild.IMWPNID)
INNER JOIN
(qryrptCBARebuildPartList5000HrRebuild
LEFT JOIN
qryrptSerialManualPartList5000HrRebuild
ON
qryrptCBARebuildPartList5000HrRebuild.IMWPNID =
qryrptSerialManualPartList5000HrRebuild.IMWPNID)
WHERE
qryrptSerialReqPartList5000HrRebuild.IMWPNID IS NULL AND
qryrptSerialManualPartList5000HrRebuild.IMWPNID IS NULL;

i am trying to get it so that the first 2 sections are joined regardless of
nulls or dupes, and the third only shows if the part isnt in the other 2. i
knew my from was going to be a problem even before i wrote it, because i have
no idea how to bracket it and group the fields.

any and all help is appreciated. :)
 
D

Douglas J. Steele

Remove the keyword ALL after UNION.

UNION eliminates duplicates, UNION ALL doesn't.
 
D

DawnTreader

Hello Doug

i got the queries to work, but now i have a bigger problem. i hit a system
resources exceeded error along with a cannot open more databases error.
basically my chain of queries got way to large.

so i decided that i could start from the ground up and use some subqueries.
it is actually a little faster than the multiple queries i had been using.
the thing is i am at a point where subqueries wont work because of the type
of subquery i am doing, a union subquery. so i cant put the query in a
subquery because of my union subquery.

so i have devised a plan to use a temporary table to store the results and
then i can query off that to build the next few stages. here is the SQL as it
stands:

INSERT INTO utblRebuildPartList
SELECT SQ.ProdID AS ProdID, SQ.Category AS Category, SQ.IMWPNID AS IMWPNID,
SQ.PartDesc AS PartDesc, SQ.Ref AS Ref, SQ.PartBlockQTY AS PartBlockQTY,
SQ.ListPrice AS ListPrice, SQ.PLID AS PLID, 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) AND
tblProductPartList.ProductID=669)

UNION ALL 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) AND
tblProductPartList.ProductID=669)

UNION ALL 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
((tblProductList.ProductID=669) AND
(tblProductPartList.IMWPartNumberID IS NULL) AND (dbo_REQUIREMENT.PART_ID IS
NULL))]. AS SQ LEFT JOIN tblMasterPartList ON SQ.IMWPNID =
tblMasterPartList.ID
ORDER BY SQ.ProdID;

there is one problem with the results. the third part of the union subquery
is giving me everything from the tblCBAPartList when all i need are the
records where the part number doesnt exist in the other 2 sections of the
Union subquery.

i know i need to add another ""frustrated outer join" unmatched query" as
John put it, but my try at the criteria isnt working. i am still getting
duplicate part numbers.
 
D

DawnTreader

Rats, i guess i should also mention that the SQL in my last post brings up 2
parameter dialogs for the criteria "(tblProductPartList.IMWPartNumberID IS
NULL) AND (dbo_REQUIREMENT.PART_ID IS NULL)"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

is this query with a subquery, a union query, normal? 1

Top