Union Query Problem

[Craig Hornish]

Hi,
I have 2 tables one is current Line orders from a Pervasive SQL database
returns 1344 records

SELECT ORDER_LINES.CUSTOMER, ORDER_LINES.DESCRIPTION, ORDER_LINES.PART
FROM ORDER_LINES
WHERE (((ORDER_LINES.FLAG_BOM)="Y" Or (ORDER_LINES.FLAG_BOM)="N"));

Second Is a history using the basic access table returns 38320 records

SELECT tmakOrder_Hist_Line.CUSTOMER, tmakOrder_Hist_Line.DESCRIPTION,
tmakOrder_Hist_Line.PART
FROM tmakOrder_Hist_Line
WHERE (((tmakOrder_Hist_Line.FLAG_BOM)="Y" Or
(tmakOrder_Hist_Line.FLAG_BOM)="N"));

Taking out the ";" and putting in Union I get 2649 records.

Any ideas of what the problem could be?

Thanks
Craig Hornish

 

[Craig Hornish]

Answering my own quetion.
Union was eliminating all duplicates if all fields matched - Used Union
All

 

[John Spencer (MVP)]

UNION does a DISTINCT on the entire selection. That means any lines that are
identical get collapsed into one. Try using UNION ALL in place of UNION. UNION
ALL does not perform the distinct action.