Union Returning Extra Rows

[Guest]

The following query is returning duplicate rows from both tblBatchHeader and
tblLines. I just want duplicates from the tblLines. How do I do this with my
query?

Result:
idsBatch
HeaderID Batch_Header
240 $X692;908 DATA PROCESSING
240 1000000001
240 1000000001
240 $X692;908 DATA PROCESSING (I don't need the extra line here.)

Query:

SELECT BH.idsBatchHeaderID, UCASE([chrDescription]) AS Batch_Header
FROM tblBatchHeader AS BH LEFT JOIN tblLLines AS L ON BH.idsBatchHeaderID =
L.lngzBatchHeaderID;

UNION ALL

SELECT lngzBatchHeaderID, lngzPatientNumberAS Lines FROM tblLLines
WHERE chrTransCode1 = "L"
order by idsBatchHeaderID;

 

[pietlinden]

Mary,

what's the difference between UNION and UNION ALL?

 

[Guest]

The UNION only returns distinct rows, no duplicates. The UNION ALL returns
all rows including non-unique rows. Here is the final query I used which
worked great.

(SELECT Sum(DT1.tally) AS total_tally FROM
(
SELECT lngzBatchHeaderID, COUNT(*) AS tally FROM tblCLines GROUP BY
lngzBatchHeaderID
UNION ALL SELECT lngzBatchHeaderID, COUNT(*) FROM tblDLines GROUP BY
lngzBatchHeaderID
UNION ALL SELECT lngzBatchHeaderID, COUNT(*) FROM tblMLines GROUP BY
lngzBatchHeaderID
UNION ALL SELECT lngzBatchHeaderID, COUNT(*) FROM tblNLines GROUP BY
lngzBatchHeaderID
UNION ALL SELECT lngzBatchHeaderID, COUNT(*) FROM tblPLines GROUP BY
lngzBatchHeaderID
UNION ALL SELECT lngzBatchHeaderID, COUNT(*) FROM tblSCLines GROUP BY
lngzBatchHeaderID
UNION ALL SELECT lngzBatchHeaderID, COUNT(*) FROM tblSPLines GROUP BY
lngzBatchHeaderID
UNION ALL SELECT lngzBatchHeaderID, COUNT(*) FROM tblTLines GROUP BY
lngzBatchHeaderID
) AS DT1
WHERE BH.idsBatchHeaderID = DT1.lngzBatchHeaderID