Show Null values from Union query (1 Viewer)

[dubez]

Hi friends,

I have a Union query that extracts the top 10 sales reps by each province. The problem is when the province does not have 10 sales reps that had business, it will not show 10 records.

This seems fairly simple but I am not sure if I should be changing my union query or the table I append the union query in to. Essentially, I have been gathering the top 10 for all reps and then left joining a default table based on the province but it has not been working. My data looks as follow:

ProvinceSales Rep IDSales Rep NameFunded AmountAB123Joe5000AB456Bob4000AB789Joe3500AB1122Jane3000AB1455Rick2500AB1788Jeremy2000AB2121Frank1500AB2454Chris1000AB2787Joel600AB3120Steve5000ON645Mary5000ON656Anne4000ON789Olga3500

In this case, Province "AB" is fine however I would like province "ON" to show as "N/A" for the subsequent 7 records for a total of 20 records instead of 13.

Any help would be greatly appreciated!
Thanks!

 

[jdorste]

Can you post your Union Query code?

 

[dubez]

sure, it is pretty long, so I have posted just 4 Unions but the remaining 12 queries follow the exact same logic with the exception of the "BD_Code". Here it is:

SELECT TOP 10 Top_10_RSP_Loans_Advisor_A.BD_Code, Top_10_RSP_Loans_Advisor_A.[Dealer-Rep_Code], Top_10_RSP_Loans_Advisor_A.Agent_Name, Top_10_RSP_Loans_Advisor_A.Dealer_Name, Top_10_RSP_Loans_Advisor_A.SumOfNet_Of_Transfer
FROM Top_10_RSP_Loans_Advisor_A
GROUP BY Top_10_RSP_Loans_Advisor_A.BD_Code, Top_10_RSP_Loans_Advisor_A.[Dealer-Rep_Code], Top_10_RSP_Loans_Advisor_A.Agent_Name, Top_10_RSP_Loans_Advisor_A.Dealer_Name, Top_10_RSP_Loans_Advisor_A.SumOfNet_Of_Transfer
HAVING (((Top_10_RSP_Loans_Advisor_A.BD_Code)="AB1"))
ORDER BY Top_10_RSP_Loans_Advisor_A.BD_Code, Top_10_RSP_Loans_Advisor_A.SumOfNet_Of_Transfer DESC;
union
SELECT TOP 10 Top_10_RSP_Loans_Advisor_A.BD_Code, Top_10_RSP_Loans_Advisor_A.[Dealer-Rep_Code], Top_10_RSP_Loans_Advisor_A.Agent_Name, Top_10_RSP_Loans_Advisor_A.Dealer_Name, Top_10_RSP_Loans_Advisor_A.SumOfNet_Of_Transfer
FROM Top_10_RSP_Loans_Advisor_A
GROUP BY Top_10_RSP_Loans_Advisor_A.BD_Code, Top_10_RSP_Loans_Advisor_A.[Dealer-Rep_Code], Top_10_RSP_Loans_Advisor_A.Agent_Name, Top_10_RSP_Loans_Advisor_A.Dealer_Name, Top_10_RSP_Loans_Advisor_A.SumOfNet_Of_Transfer
HAVING (((Top_10_RSP_Loans_Advisor_A.BD_Code)="AB2"))
ORDER BY Top_10_RSP_Loans_Advisor_A.BD_Code, Top_10_RSP_Loans_Advisor_A.SumOfNet_Of_Transfer DESC;
union
SELECT TOP 10 Top_10_RSP_Loans_Advisor_A.BD_Code, Top_10_RSP_Loans_Advisor_A.[Dealer-Rep_Code], Top_10_RSP_Loans_Advisor_A.Agent_Name, Top_10_RSP_Loans_Advisor_A.Dealer_Name, Top_10_RSP_Loans_Advisor_A.SumOfNet_Of_Transfer
FROM Top_10_RSP_Loans_Advisor_A
GROUP BY Top_10_RSP_Loans_Advisor_A.BD_Code, Top_10_RSP_Loans_Advisor_A.[Dealer-Rep_Code], Top_10_RSP_Loans_Advisor_A.Agent_Name, Top_10_RSP_Loans_Advisor_A.Dealer_Name, Top_10_RSP_Loans_Advisor_A.SumOfNet_Of_Transfer
HAVING (((Top_10_RSP_Loans_Advisor_A.BD_Code)="BC1"))
ORDER BY Top_10_RSP_Loans_Advisor_A.BD_Code, Top_10_RSP_Loans_Advisor_A.SumOfNet_Of_Transfer DESC;
union
SELECT TOP 10 Top_10_RSP_Loans_Advisor_A.BD_Code, Top_10_RSP_Loans_Advisor_A.[Dealer-Rep_Code], Top_10_RSP_Loans_Advisor_A.Agent_Name, Top_10_RSP_Loans_Advisor_A.Dealer_Name, Top_10_RSP_Loans_Advisor_A.SumOfNet_Of_Transfer
FROM Top_10_RSP_Loans_Advisor_A
GROUP BY Top_10_RSP_Loans_Advisor_A.BD_Code, Top_10_RSP_Loans_Advisor_A.[Dealer-Rep_Code], Top_10_RSP_Loans_Advisor_A.Agent_Name, Top_10_RSP_Loans_Advisor_A.Dealer_Name, Top_10_RSP_Loans_Advisor_A.SumOfNet_Of_Transfer
HAVING (((Top_10_RSP_Loans_Advisor_A.BD_Code)="BC2"))
ORDER BY Top_10_RSP_Loans_Advisor_A.BD_Code, Top_10_RSP_Loans_Advisor_A.SumOfNet_Of_Transfer DESC;

thanks for the help!