Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 445
  • Last Modified:

Possible to get distinct record only in a union query

Here is my union query coming from two select queries...

select * from qryTestNumber1
where partN = Forms!frmShipments!cboPartN
UNION select * from qryTestNumber2
where partN = Forms!frmShipments!cboPartN;

But I am getting two records in the result of the union query if both select queries have the same result.  I only want to see one of the records in the result.

Is there a way to do this?
0
SteveL13
Asked:
SteveL13
  • 5
  • 5
  • 2
  • +3
1 Solution
 
jerryb30Commented:
Are all fields in both queries the same values in terms of content? Order of fields?

If so, it should naturally give only distinct results.

Can you post the SQL of the 2 queries?
0
 
SteveL13Author Commented:
Here's the first qry:

SELECT tblFinGoods.PartN, tblOpenOrders.Ack_Date, tblOpenOrders.Purchase_Order, tblOpenOrders.Order_Qty, tblOpenOrders.RecID, tblFinGoods.Serial, tblPartsMasters.Serial, tblOpenOrders.AppliedInvQty, tblOpenOrders.Customer_1, tblOpenOrders.Complete
FROM (tblFinGoods LEFT JOIN tblOpenOrders ON tblFinGoods.OpenOrdRecID = tblOpenOrders.RecID) LEFT JOIN tblPartsMasters ON tblOpenOrders.Part_No = tblPartsMasters.Part_No
WHERE (((tblFinGoods.PartN)=[Forms]![frmShipments]![cboPartN3]) AND ((tblOpenOrders.Complete)=False))
ORDER BY tblFinGoods.PartN, tblOpenOrders.Ack_Date;

And here's the 2nd query:

SELECT tblFinished.PartN, tblOpenOrders.Ack_Date, tblOpenOrders.Purchase_Order, tblOpenOrders.Order_Qty, tblOpenOrders.RecID, tblFinished.Serial, tblPartsMasters.Serial, tblOpenOrders.AppliedInvQty, tblOpenOrders.Customer_1, tblOpenOrders.Complete
FROM (tblFinished LEFT JOIN tblOpenOrders ON tblFinished.OpenOrdRecID = tblOpenOrders.RecID) INNER JOIN tblPartsMasters ON tblFinished.PartN = tblPartsMasters.Part_No
WHERE (((tblFinished.PartN)=[Forms]![frmShipments]![cboPartN3]) AND ((tblOpenOrders.Complete)=False))
ORDER BY tblFinished.PartN, tblOpenOrders.Ack_Date;

And here's the union query (revised sine I put the form criteria in the first two queries:

select * from qryTestNumber1
UNION select * from qryTestNumber2;
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
UNION returns only distinct rows, which would EXclude duplicates.

UNION ALL returns all rows, which would INclude duplicates.

I agree with jerryb30, there has to be a column somewhere that contains a different value that is causing the query to return both rows.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
jerryb30Commented:
And each query returns the same values for each field in a single record, when run separately?
0
 
SteveL13Author Commented:
Yes...  each query returns the same values for each field in a single record, when run separately  and in the exact same order.
0
 
SteveL13Author Commented:
Attached:  Screen shots of the two queries.
Clip1.jpg
Clip2.jpg
0
 
Rey Obrero (Capricorn1)Commented:
try running this query


SELECT T.PartN, T.Ack_Date, T.Purchase_Order, T.Order_Qty, T.RecID, T.Serial, tblPartsMasters.Serial, T.AppliedInvQty, T.Customer_1, T.Complete
From
(
SELECT tblFinGoods.PartN, tblOpenOrders.Ack_Date, tblOpenOrders.Purchase_Order, tblOpenOrders.Order_Qty, tblOpenOrders.RecID, tblFinGoods.Serial, tblPartsMasters.Serial, tblOpenOrders.AppliedInvQty, tblOpenOrders.Customer_1, tblOpenOrders.Complete
FROM (tblFinGoods LEFT JOIN tblOpenOrders ON tblFinGoods.OpenOrdRecID = tblOpenOrders.RecID) LEFT JOIN tblPartsMasters ON tblOpenOrders.Part_No = tblPartsMasters.Part_No
WHERE (((tblFinGoods.PartN)=Eval("[Forms]![frmShipments]![cboPartN3]") AND ((tblOpenOrders.Complete)=False))
ORDER BY tblFinGoods.PartN, tblOpenOrders.Ack_Date
UNION
SELECT tblFinished.PartN, tblOpenOrders.Ack_Date, tblOpenOrders.Purchase_Order, tblOpenOrders.Order_Qty, tblOpenOrders.RecID, tblFinished.Serial, tblPartsMasters.Serial, tblOpenOrders.AppliedInvQty, tblOpenOrders.Customer_1, tblOpenOrders.Complete
FROM (tblFinished LEFT JOIN tblOpenOrders ON tblFinished.OpenOrdRecID = tblOpenOrders.RecID) INNER JOIN tblPartsMasters ON tblFinished.PartN = tblPartsMasters.Part_No
WHERE (((tblFinished.PartN)=Eval("[Forms]![frmShipments]![cboPartN3]") AND ((tblOpenOrders.Complete)=False))
ORDER BY tblFinished.PartN, tblOpenOrders.Ack_Date
) AS T
Group by T.PartN, T.Ack_Date, T.Purchase_Order, T.Order_Qty, T.RecID, T.Serial, tblPartsMasters.Serial, T.AppliedInvQty, T.Customer_1, T.Complete



see if you will duplicates
0
 
jerryb30Commented:
try
select distinct * from qryTestNumber1
union
select distinct * from qryTestNumber2

Given what you stated, this should not be happening, but it IS possible that a field is not being evaluated exactly the same.

Are the data types of fields in tblFinished and tblFinGoods the same?  Maybe there is some white space, based on field lengths, or a stray bit in a numeric field (maybe one being a single and another being a double.)
0
 
SteveL13Author Commented:
Capricorn1:  Syntax error in from clause
0
 
SteveL13Author Commented:
Jerryb30:  Still get duplicate records
0
 
jerryb30Commented:
Not linked to external database? Exact same structure in the 2 tables?
0
 
jerryb30Commented:
Oh, any memo fields?
0
 
Rey Obrero (Capricorn1)Commented:
ok, try this


SELECT T.PartN, T.Ack_Date, T.Purchase_Order, T.Order_Qty, T.RecID, T.Serial, T.Serial, T.AppliedInvQty, T.Customer_1, T.Complete
From
(
SELECT tblFinGoods.PartN, tblOpenOrders.Ack_Date, tblOpenOrders.Purchase_Order, tblOpenOrders.Order_Qty, tblOpenOrders.RecID, tblFinGoods.Serial, tblPartsMasters.Serial, tblOpenOrders.AppliedInvQty, tblOpenOrders.Customer_1, tblOpenOrders.Complete
FROM (tblFinGoods LEFT JOIN tblOpenOrders ON tblFinGoods.OpenOrdRecID = tblOpenOrders.RecID) LEFT JOIN tblPartsMasters ON tblOpenOrders.Part_No = tblPartsMasters.Part_No
WHERE (((tblFinGoods.PartN)=Eval("[Forms]![frmShipments]![cboPartN3]") AND ((tblOpenOrders.Complete)=False))
ORDER BY tblFinGoods.PartN, tblOpenOrders.Ack_Date
UNION
SELECT tblFinished.PartN, tblOpenOrders.Ack_Date, tblOpenOrders.Purchase_Order, tblOpenOrders.Order_Qty, tblOpenOrders.RecID, tblFinished.Serial, tblPartsMasters.Serial, tblOpenOrders.AppliedInvQty, tblOpenOrders.Customer_1, tblOpenOrders.Complete
FROM (tblFinished LEFT JOIN tblOpenOrders ON tblFinished.OpenOrdRecID = tblOpenOrders.RecID) INNER JOIN tblPartsMasters ON tblFinished.PartN = tblPartsMasters.Part_No
WHERE (((tblFinished.PartN)=Eval("[Forms]![frmShipments]![cboPartN3]") AND ((tblOpenOrders.Complete)=False))
ORDER BY tblFinished.PartN, tblOpenOrders.Ack_Date
) AS T
Group by T.PartN, T.Ack_Date, T.Purchase_Order, T.Order_Qty, T.RecID, T.Serial, tblPartsMasters.Serial, T.AppliedInvQty, T.Customer_1, T.Complete
0
 
aikimarkCommented:
please check the exact value of the date fields.  If there is a time-of-day (fractional value) with these dates, they might be different.
0
 
hnasrCommented:
try:

SELECT DISTINCT * FROM (your union queries)
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 5
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now