By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,190 Members | 783 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,190 IT Pros & Developers. It's quick & easy.

union query - strange unwanted grouping

P: n/a

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
Share this Question
Share on Google+
5 Replies


P: n/a
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

P: n/a
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

P: n/a

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

P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.