G
Gaetanm via AccessMonster.com
I appear to be losing records in a union query results
My Union query brings up 13 parts for Job 120.
If I query Parts separately I get 11 parts
If I query Myob separately I get 5 parts
In my separate Parts query I have 4 parts that are
identical but the Union only shows one.
16 Items is the correct answer. Does a union
Get rid of duplicates? If so what would be the solution
Any ideas? Below is my SQL for the union
SELECT [Job Sheet].[Job ID], Parts.ProductDescription, Parts.Quanity, ([Unit
Price]*parts.[Mark up])+([unit Price]) AS Unit_Markup, ([Unit_markup]*
[Quanity]) AS Extended_cost, [Job Sheet].Notes, Customers.CompanyName
FROM ((Customers INNER JOIN [Job Sheet] ON Customers.CustomerID = [Job Sheet].
CustomerID) INNER JOIN Parts ON [Job Sheet].[Job ID] = Parts.[Job ID]) INNER
JOIN Started_Jobs_billable ON [Job Sheet].[Job ID] = Started_Jobs_billable.
[Job ID]
WHERE ((([Job Sheet].[Job ID])=[FORMS]![frm_Invoice]![cboJob_ID]))
UNION SELECT [Myob_cards query].JobNumber, [Myob_cards query].Description,
[Myob_cards query].Quantity, [Myob_cards query].Price, [Myob_cards query].
Extended_Cost, [Job Sheet].Notes, Customers.CompanyName
FROM Customers INNER JOIN ([Job Sheet] INNER JOIN [Myob_cards query] ON [Job
Sheet].Job_Number = [Myob_cards query].JobNumber) ON Customers.CustomerID =
[Job Sheet].CustomerID
WHERE ((([Myob_cards query].JobNumber)=[FORMS]![frm_Invoice]![cboJob_ID]));
My Union query brings up 13 parts for Job 120.
If I query Parts separately I get 11 parts
If I query Myob separately I get 5 parts
In my separate Parts query I have 4 parts that are
identical but the Union only shows one.
16 Items is the correct answer. Does a union
Get rid of duplicates? If so what would be the solution
Any ideas? Below is my SQL for the union
SELECT [Job Sheet].[Job ID], Parts.ProductDescription, Parts.Quanity, ([Unit
Price]*parts.[Mark up])+([unit Price]) AS Unit_Markup, ([Unit_markup]*
[Quanity]) AS Extended_cost, [Job Sheet].Notes, Customers.CompanyName
FROM ((Customers INNER JOIN [Job Sheet] ON Customers.CustomerID = [Job Sheet].
CustomerID) INNER JOIN Parts ON [Job Sheet].[Job ID] = Parts.[Job ID]) INNER
JOIN Started_Jobs_billable ON [Job Sheet].[Job ID] = Started_Jobs_billable.
[Job ID]
WHERE ((([Job Sheet].[Job ID])=[FORMS]![frm_Invoice]![cboJob_ID]))
UNION SELECT [Myob_cards query].JobNumber, [Myob_cards query].Description,
[Myob_cards query].Quantity, [Myob_cards query].Price, [Myob_cards query].
Extended_Cost, [Job Sheet].Notes, Customers.CompanyName
FROM Customers INNER JOIN ([Job Sheet] INNER JOIN [Myob_cards query] ON [Job
Sheet].Job_Number = [Myob_cards query].JobNumber) ON Customers.CustomerID =
[Job Sheet].CustomerID
WHERE ((([Myob_cards query].JobNumber)=[FORMS]![frm_Invoice]![cboJob_ID]));