Union Query Returning Different Data

[deluga.69]

I have six select queries that pull the same Top 5 data for different distributors. They all work perfectly as individual queries but when I join them in a Union query i no longer get the the Top 5 returns.

Any ideas? Here's the first union (there are four more):

SELECT TOP 5 tbl_Distributor_Report_Names.dist_display_name, dbo_pictures.picture_name, Sum(dbo_grosses_master.gross_amount) AS release_amount
FROM tbl_Date_Range, (((dbo_grosses_master INNER JOIN dbo_releases ON dbo_grosses_master.release_id = dbo_releases.release_id) INNER JOIN dbo_pictures ON dbo_releases.picture_id = dbo_pictures.picture_id) INNER JOIN dbo_labels ON dbo_pictures.label_id = dbo_labels.label_id) INNER JOIN tbl_Distributor_Report_Names ON dbo_labels.distributor_id = tbl_Distributor_Report_Names.distributor_id


WHERE (((dbo_grosses_master.gross_start_date) Between [start_date] And [end_date]))
GROUP BY tbl_Distributor_Report_Names.dist_display_name, dbo_pictures.picture_name, dbo_labels.distributor_id, dbo_grosses_master.release_id
HAVING (((dbo_labels.distributor_id)=1))
UNION ALL
SELECT TOP 5 tbl_Distributor_Report_Names.dist_display_name, dbo_pictures.picture_name, Sum(dbo_grosses_master.gross_amount) AS release_amount
FROM tbl_Date_Range, (((dbo_grosses_master INNER JOIN dbo_releases ON dbo_grosses_master.release_id = dbo_releases.release_id) INNER JOIN dbo_pictures ON dbo_releases.picture_id = dbo_pictures.picture_id) INNER JOIN dbo_labels ON dbo_pictures.label_id = dbo_labels.label_id) INNER JOIN tbl_Distributor_Report_Names ON dbo_labels.distributor_id = tbl_Distributor_Report_Names.distributor_id
WHERE (((dbo_grosses_master.gross_start_date) Between [start_date] And [end_date]))
GROUP BY tbl_Distributor_Report_Names.dist_display_name, dbo_pictures.picture_name, dbo_labels.distributor_id, dbo_grosses_master.release_id
HAVING (((dbo_labels.distributor_id)=3))
ORDER BY release_amount;

[apr pillai]

Use the TOP 5 Queries themselves as Source for the UNION Query rather than copying it's SQL.

If you look at the first SQL, in the example given above, the ORDER BY Clause is missing. So the TOP 5 clause of the first Query has no meaning.

[deluga.69]

Well, after the proverbial "duh" moment, I applied your suggestion and it worked perfectly.

Thank you so much!