Union Confusion

[tom]

I have a fairly complex union query that is behaving
in a strange manner.

Basically if I run the first select statement alone,
everything runs as expected.

If I union the first select statement with a second
statement, a record from the first select statement
is deleted. If I use a union all no records are deleted.

The record that is deleted is not the same as another
record, although the information that the query is
instructed to show is the same.

 

[John Vinson]

If I union the first select statement with a second
statement, a record from the first select statement
is deleted. If I use a union all no records are deleted.

The record that is deleted is not the same as another
record, although the information that the query is
instructed to show is the same.

This is exactly how UNION queries are designed to work. The record
isn't actually deleted from the table; instead, Access assembles a
recordset of all the SELECTed fields from each query in the UNION,
removes duplicates, and presents you with the de-duplicated list.

If there are other fields in the table that you didn't include in the
UNION query, they are irrelevant as regards the duplication; two
records are considered duplicates if the fields that you include in
the query are identical (even if other fields that you didn't select
differ).

John W. Vinson[MVP]