×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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.

CODE --> VBA

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.

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

Try:

CODE

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

---- Andy

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

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

(OP)
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!!!!

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

(OP)
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.

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

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:
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*")
 
but I would even go further and use just one Select:
SELECT CASE Reps.Rep WHEN yada yada yada ... 
smile

---- Andy

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

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

(OP)
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!

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

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

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

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

---- Andy

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

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

(OP)
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?

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

(OP)
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!

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

Quote (TheresAlwaysAWay)

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 ponder

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

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

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

Quote (TheresAlwaysAWay)

needed two reps to be combined and considered as one entry

which is:
Reps.Rep
Laura Lake
Laura Lake
Laura Lake - 2020 Promo
Laura Lake
Laura Lake - 2020 Promo 

should all be just Laura Lake, 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:

SELECT IIF(Reps.Rep = "Laura Lake - 2020 Promo", "Laura Lake", Reps.Rep) As Rep,
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*"

wiggle

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

---- Andy

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

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

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close