473,287 Members | 1,643 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

union query - strange unwanted grouping


I'm having a problem with a union query, two simple queries joined with
a union statement. It's created in code based on parameters. Users were
noticing some inconsistant data and when I analysed the query produced
and opened it from a MS Query it started giving strange results.

The first query when run alone returns 22 records, some of which have
identical values in all fields. This is 100% correct.
The second query returns nothing. This is also 100%

However when the two are joined in a Union query, the output is 20
records - the records with identical data have been bounced down to
one, like the query was grouped on all.

I know it's hard without playing with it yourself, but has anyone seen
anything like this before?

any help would be apprecitated - I'm starting to lose the plot a little!

Jul 4 '06 #1
5 2249
from the UNION Operation Help topic in Access Help:
"By default, no duplicate records are returned when you use a UNION
operation; however, you can include the ALL predicate to ensure that all
records are returned. This also makes the query run faster."

hth
"BillCo" <co**********@gmail.comwrote in message
news:11**********************@h44g2000cwa.googlegr oups.com...
>
I'm having a problem with a union query, two simple queries joined with
a union statement. It's created in code based on parameters. Users were
noticing some inconsistant data and when I analysed the query produced
and opened it from a MS Query it started giving strange results.

The first query when run alone returns 22 records, some of which have
identical values in all fields. This is 100% correct.
The second query returns nothing. This is also 100%

However when the two are joined in a Union query, the output is 20
records - the records with identical data have been bounced down to
one, like the query was grouped on all.

I know it's hard without playing with it yourself, but has anyone seen
anything like this before?

any help would be apprecitated - I'm starting to lose the plot a little!

Jul 4 '06 #2
Change:
UNION
to:
UNION ALL

A UNION query is designed to deduplicate.
UNION ALL does not remove the duplicates.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BillCo" <co**********@gmail.comwrote in message
news:11**********************@h44g2000cwa.googlegr oups.com...
>
I'm having a problem with a union query, two simple queries joined with
a union statement. It's created in code based on parameters. Users were
noticing some inconsistant data and when I analysed the query produced
and opened it from a MS Query it started giving strange results.

The first query when run alone returns 22 records, some of which have
identical values in all fields. This is 100% correct.
The second query returns nothing. This is also 100%

However when the two are joined in a Union query, the output is 20
records - the records with identical data have been bounced down to
one, like the query was grouped on all.

I know it's hard without playing with it yourself, but has anyone seen
anything like this before?

any help would be apprecitated - I'm starting to lose the plot a little!

Jul 4 '06 #3

Thanks Allen and Tina!!!
you'd think I'd know that thing like that - egg on my face!!!

....fantastic - problem solved

Allen Browne wrote:
Change:
UNION
to:
UNION ALL

A UNION query is designed to deduplicate.
UNION ALL does not remove the duplicates.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"BillCo" <co**********@gmail.comwrote in message
news:11**********************@h44g2000cwa.googlegr oups.com...

I'm having a problem with a union query, two simple queries joined with
a union statement. It's created in code based on parameters. Users were
noticing some inconsistant data and when I analysed the query produced
and opened it from a MS Query it started giving strange results.

The first query when run alone returns 22 records, some of which have
identical values in all fields. This is 100% correct.
The second query returns nothing. This is also 100%

However when the two are joined in a Union query, the output is 20
records - the records with identical data have been bounced down to
one, like the query was grouped on all.

I know it's hard without playing with it yourself, but has anyone seen
anything like this before?

any help would be apprecitated - I'm starting to lose the plot a little!
Jul 4 '06 #4
BillCo wrote:
I know it's hard without playing with it yourself, but has anyone seen
anything like this before?
Yes, try using UNION ALL and see if that helps?

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Jul 4 '06 #5
I think you could put something like this

select Col 1 , Col 2 from Table 1
union all
select Col 1, Col 2 from Table 2

All the records are going to appear , because of the clausule all .

Try it !

Falco.
BillCo escreveu:
I'm having a problem with a union query, two simple queries joined with
a union statement. It's created in code based on parameters. Users were
noticing some inconsistant data and when I analysed the query produced
and opened it from a MS Query it started giving strange results.

The first query when run alone returns 22 records, some of which have
identical values in all fields. This is 100% correct.
The second query returns nothing. This is also 100%

However when the two are joined in a Union query, the output is 20
records - the records with identical data have been bounced down to
one, like the query was grouped on all.

I know it's hard without playing with it yourself, but has anyone seen
anything like this before?

any help would be apprecitated - I'm starting to lose the plot a little!
Jul 6 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: (Pete Cresswell) | last post by:
I've got some SQL that works as far as returning a recordset from a series of UNION statements. viz: SELECT whatever UNION this UNION that UNION other
0
by: Len Coleson | last post by:
I am at wits end with this. I have a transaction report that has a union query as a record source that asks date parameters from an input form. The sub report is based on a variation of the same...
4
by: Mark | last post by:
Hi all, I am currently in the design stages of a database for work. I have come up with a way to get informaion I need using a union query but was wandering what effects this will have on...
12
by: Susan Bricker | last post by:
For those of you who have been following my posts - they all pertain to a Dog Competition Organization's Database. There are three classes that the dogs can participate: NOVICE, OPEN, and...
1
by: suslikovich | last post by:
Hi all, I am getting this error when insert values from one table to another in the first table the values are varchar (10). In the second they are datetime. The format of the data is mm/dd/yyyy...
1
by: Arayeshi | last post by:
Hi all, I have a two tables with same structure in DB2 Ver 6 on OS/390 1- CurrPay(CustomerId, BnkId, BranchCode, PayDate, Amount) for Current Period Customers Payments 2- PayHist(CustomerId,...
4
by: janko.klemensek | last post by:
Can I Group by the Union in one query or must I write two queries for this (one for union and second for group by)?
11
by: Israel | last post by:
I've trying to write a query that seems like it should be simple but for some reason my attempts are not working. This is really a general SQL quesion and doesn't pertain to MySQL but I couldn't...
2
by: benhaynes | last post by:
I am writing a query to create a tree menu, it pulls from a table of music "tracks". In this database there are four "sub_genre" fields for each track, and I need to create a list of all used...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.