TheresAlwaysAWay
Programmer
I have a longtime DB in A19 and SQLServer 19 with 20+ users.
We have a table listing our representatives appropriately called Reps. They are not the actual sales people, but their names appear on each sales record because they get credit for them.
El Jefe commissioned me to create a new report which had tons of calculations such as YTD and LYTD quote and policy totals, and a ton of other things so that he could review and compare performances.
I created exactly what he wanted, and after everything was working perfectly he realized he needed two reps to be combined and considered as one entry. I created two Select queries, one which showed everyone except the two in question, and the other showing just the two. I worked out how to combine the two as one record and then I put the two queries back together again with a Union. However when you look at the totals for anyone in the "except" query when run alone and they don't match the same person when run with the Union.
The same thing happens with the two reps being combined. If I compare the numbers in the individual query with the Union results they don't match.
This is the Union SQL.
If I run the two select queries individually the numbers don't match the Union results. Does anyone have a guess?
I'm always greatly appreciative to all of you who give of your time and experience so selflessly.
Thanks for your help in advance.
We have a table listing our representatives appropriately called Reps. They are not the actual sales people, but their names appear on each sales record because they get credit for them.
El Jefe commissioned me to create a new report which had tons of calculations such as YTD and LYTD quote and policy totals, and a ton of other things so that he could review and compare performances.
I created exactly what he wanted, and after everything was working perfectly he realized he needed two reps to be combined and considered as one entry. I created two Select queries, one which showed everyone except the two in question, and the other showing just the two. I worked out how to combine the two as one record and then I put the two queries back together again with a Union. However when you look at the totals for anyone in the "except" query when run alone and they don't match the same person when run with the Union.
The same thing happens with the two reps being combined. If I compare the numbers in the individual query with the Union results they don't match.
This is the Union SQL.
Code:
SELECT Reps.Rep, AllVehicles.QTDate, AllVehicles.EffDate, Reps.PartTime
FROM Reps LEFT JOIN AllVehicles ON Reps.Rep = AllVehicles.Rep
WHERE (((Reps.Rep)<>"Laura Lake - 2020 Promo" And (Reps.Rep)<>"Laura Lake") AND ((Reps.Inactive)=False) AND ((AllVehicles.InsCo) Not Like "Can*"))
UNION
SELECT "Laura Lake" AS Rep, AllVehicles.QTDate, AllVehicles.EffDate, Reps.PartTime
FROM Reps LEFT JOIN AllVehicles ON Reps.Rep = AllVehicles.Rep
WHERE (((Reps.Rep)="Laura Lake" Or (Reps.Rep)="Laura Lake - 2020 Promo") AND ((Reps.Inactive)=False) AND ((AllVehicles.InsCo) Not Like "Can*"));
If I run the two select queries individually the numbers don't match the Union results. Does anyone have a guess?
I'm always greatly appreciative to all of you who give of your time and experience so selflessly.
Thanks for your help in advance.