Losing rows in Union of 2 queries

[jimatqsi]

Using an Access front-end with a SQL Server 2005 backend. I have a query that returns 2 rows. I have a very similar query that returns zero rows. When I union the 2 queries I get one row.

The two rows that are returned by the one query are identical in content. Same customer, same item, same quantity and price.

There is no grouping in either query or the union of the two queries.

If I add invoice number to the Select statements, the two identical rows become unique and the union returns two rows, as I think it should.

Is this an Access bug, or is there something I need to know about how Unions work?

Thanks,
Jim

[MikeTheBike]

Hi

I think you need UNION ALL, not just UNION

??

MTB

[jimatqsi]

Thanks very much. I didn't know about Union All, or that Union eliminated duplicate entries. Gee, I wonder how many bugs I created with that?

Jim

[NeoPa]

I don't say this with criticism in mind at all, but you can find some fairly helpful info on Jet SQL, with its options etc, using Finding Jet SQL Help. It's actually not too easy to stumble across, so I hope having it pointed out is helpful.

[jimatqsi]

Thanks for pointing that insight out to me, Ade. I think that's going to be very helpful. I always used the VBA help and didn't even realize this other was available.

Jim

[NeoPa]

Always a pleasure Jim. I remember my surprise when I found out how much help was available than I'd previously realised :-)