Union does not show all records

  • Thread starter Thread starter Gaetanm via AccessMonster.com
  • Start date Start date
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]));
 
Hi.
Does a union
Get rid of duplicates?
Yes.

If so what would be the solution
Click to expand...

Use UNION ALL in your query syntax, instead of just UNION. The query will
run faster, too, because the duplicates don't need to be removed from the
displayed rows.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
Congratulations on the MVP award Gunny! Nice to see some more women in the
Access MVP ranks.
 
Thanks Gunny that was it

Gaetanm

'69 Camaro said:
Hi.


Use UNION ALL in your query syntax, instead of just UNION. The query will
run faster, too, because the duplicates don't need to be removed from the
displayed rows.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
Click to expand...
 
JH said:
Congratulations on the MVP award Gunny!
Click to expand...

Thank you very much. But I'm at a loss as to who you are, because I can't
think of anyone I know of with your initials. You created a brand new
Windows Live ID identity to post your message, so there aren't any hints,
either. Would you please shed some light on the mystery?

Thanks.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
Back
Top