Problem with UNION Command

[April15Hater]

Hi,

I'm trying to make use of the UNION SQL command, but I can't quite seem to get it to work. Here's my Code:

SELECT tblProductionTracking.ProductionTrackingID, tblProductionTracking.FunctionTrackingID, tblProductionTracking.TrackingNumber, tblProductionTracking.TrackingOnly, tblProductionInput.ProductionID
FROM tblProductionInput INNER JOIN tblProductionTracking ON tblProductionInput.ProductionID = tblProductionTracking.ProductionID
WHERE (((tblProductionTracking.FunctionTrackingID)=[Forms]![frmProductionStep3b]![cboTrackableSel]) AND ((tblProductionTracking.ProductionID)=[Forms]![frmProductionStep3b]![txtProductionID]))
UNION 
SELECT tblProductionStep3blstTracking.ProductionTrackingID, tblProductionStep3blstTracking.FunctionTrackingID, tblProductionStep3blstTracking.TrackingNumber, tblProductionStep3blstTracking.TrackingOnly, tblProductionStep3blstTracking.ProductionID 
FROM tblProductionStep3blstTracking WHERE (((tblProductionTracking.FunctionTrackingID)=[Forms]![frmProductionStep3b]![cboTrackableSel]) AND ((tblProductionTracking.ProductionID)=[Forms]![frmProductionStep3b]![txtProductionID]));

WhenI take out the union and the second query, it returns a record (working right). But when I add the Union, it loses the one record. Any ideas of what I could be doing wrong??

 

[boblarson]

If a duplicate exists between the two, the use of UNION will only return one of the duplicates. If you want all of them to be returned you need to use UNION ALL.

 

[pbaldy]

See changing

UNION

to

UNION ALL

fixes it.

 

[April15Hater]

Nope :-/ What's weird is that the first half of the query in isolation returns 1 record. WHen I add the Union and the second query, there are 0 records, so it is limiting it? By the way, I don't think it makes a lot of difference, but this is in a list box.

Thanks,
Joe

 

[pbaldy]

Can you post a sample? I can't think of anything that would cause that behavior.

 

[April15Hater]

I apologize, I'm not sure what you mean by sample.

Tell me about it, that's why i'm so stumped.

 

[pbaldy]

An mdb with the tables and query that we can play with. Obviously it shouldn't have any personal data in it.

 

[April15Hater]

I gotcha. There's an FE and a BE, but there is confid data in there. Not a big deal to send to you directly, but I just don't want to post it on the internet. Could you PM your email?

 

[pbaldy]

What values would go in the form?