Union query provides different reslt than when summing the combined queries individually 1

[TheresAlwaysAWay]

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.

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.

 

[Andrzejek]

Try:

...
UNION ALL
...

and see what happens. Do you get what you need?

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TheresAlwaysAWay]

It seems so!

I had 103k records returned from original UNION, and 297k records returned from UNION ALL.

I haven't checked specifically yet but it's a world of difference. Can you please explain UNION ALL, what it does, and when it should be used?

Meantime I'm going to run tests with the records to see if numbers match what they should, but there's a huge difference already.

THANKS!!!!

 

[TheresAlwaysAWay]

Since I didn't know what to do and wasn't sure if or when I might get a reply here, I resorted to creating a Make Table query and an Append query and put all the records into a table. That table was working as the record source, giving me the desired results. So I ran the table and compared the results with the query. Both returned 299,727 records so I am confident that it is working right. I can't thank you enough.

 

[Andrzejek]

UNION works pretty much like DISTINCT. If you have multiple records, they will be 'combined' into one (no duplicates)
UNION ALL will give you ALL records from first Select and ALL records from the second, duplicates or not.

BTW, this could look like this:[blue]
[pre]
SELECT Reps.Rep, AllVehicles.QTDate, AllVehicles.EffDate, Reps.PartTime
FROM Reps LEFT JOIN AllVehicles ON Reps.Rep = AllVehicles.Rep
WHERE Reps.Rep NOT IN ("Laura Lake - 2020 Promo", "Laura Lake")
AND Reps.Inactive = False
AND AllVehicles.InsCo Not Like "Can*"
UNION ALL
SELECT "Laura Lake" AS Rep, AllVehicles.QTDate, AllVehicles.EffDate, Reps.PartTime
FROM Reps LEFT JOIN AllVehicles ON Reps.Rep = AllVehicles.Rep
WHERE Reps.Rep IN ("Laura Lake", "Laura Lake - 2020 Promo")
AND Reps.Inactive = False
AND AllVehicles.InsCo Not Like "Can*")
[/pre][/blue]but I would even go further and use just one Select:
[pre]
SELECT [blue]CASE Reps.Rep WHEN yada yada yada[/blue] ...[/pre]

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TheresAlwaysAWay]

I can't thank you enough. One very minor change made hours of my work evaporate!

I'd love to buy ya a beer if you're ever in my neck of the woods in Southern CA!

 

[Andrzejek]

Maybe you do want to return to UNION with 103k records.
Your 297k records - you may have 194k 'repeated' records of your 'really needed' 103k recs.

Check your data.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

hours of my work' vs 10 minutes for an answer - that is the power of TT

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TheresAlwaysAWay]

Another question on a different subject. Is there a way to make Access display a + sign in front of positive integers, just as it automatically displays the - sign for negatives?

 

[TheresAlwaysAWay]

Maybe you do want to return to UNION with 103k records.
Your 297k records - you may have 194k 'repeated' records of your 'really needed' 103k recs.

Check your data.


Actually, I have checked it and I do in fact need all the data to draw from. The numbers are working correctly now!

 

[Andrzejek]

display a + sign in front of positive integers

Yes, you can do it, but... why?

-125 is a number, but +125 would be a text, which means you canNOT use it in any calculations, unless you would include some complicated text to convert it back to a number.
Why would you want it

Edit:
I would go with other people's suggestion (in the other thread) of changing the format, and not do that in your Select SQL.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

You know, after taking a closer look at your UNION (ALL) Selects, and the requirement of:

needed two reps to be combined and considered as one entry

which is:
[pre]Reps.Rep
Laura Lake
Laura Lake
Laura Lake - 2020 Promo
Laura Lake
Laura Lake - 2020 Promo[/pre]

should all be just [tt]Laura Lake[/tt], right?
If so, then you just need to 'convert' "Laura Lake - 2020 Promo" to "Laura Lake" and you are done.

Consider this (one) Select SQL:
[tt]
SELECT [blue]IIF(Reps.Rep = "Laura Lake - 2020 Promo", "Laura Lake", Reps.Rep) As Rep[/blue],
AllVehicles.QTDate, AllVehicles.EffDate, Reps.PartTime
FROM Reps LEFT JOIN AllVehicles ON Reps.Rep = AllVehicles.Rep
WHERE Reps.Inactive = False AND AllVehicles.InsCo Not Like "Can*"[/tt]


Two additional points:
1. I eliminated all "(" and ")", there are just confusing and not needed
2. Field [tt]Inactive[/tt] with True/False, confusing (to me). Since this is a Boolean True/False field, I would name it [blue][tt]IsActive[/tt][/blue] (and probably reverse True/False values), but that may be just me...

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[TheresAlwaysAWay]

I got sidetracked off of this thread once everything was working the way I wanted, but I really appreciate the additional thoughts that you offered. They make sense and definitely a better way to do things. Life is a learning experience, isn’t it?

Thank you again for all of your assistance. I gave you a star for your post because you definitely deserve it.