Duplicate Query Reporting Unique Values...

[Tomfernandez1]

Greetings,

I have a query deriving from a Union Query that has the following fields:

Passenger Serial #
First Name
Last Name


Van #
Month

The query essentially pulls data from multiple tables that represent 12 months in a year, and will show duplicate data by Passenger Serial #, First Name....and so on.
I ultimately want a Find Duplicates query to show the number of people using the same Passenger Serial # over a year. The problem is Months will produce duplicates of the same information by different months.
How do I develop a query that would show duplicates for Passenger Serial # and 'different' First Name and Last Name, and show Month?

Example of what I want the query to show:
1234567 Bill Smith 554 Jan
1234567 Joe Brown 443 Mar

In the example, Bill Smith also has duplicates in different months (Feb, Mar), but information on Joe Brown gets reported since Joe is using the same Passenger Serial #.

Any advice would be appreciated!

Tommy

[pbaldy]

For starters, it's almost certainly a mistake to have the data in monthly tables like that. In any case, try changing UNION to UNION ALL. UNION by default will eliminate duplicates, UNION ALL will not.

[Tomfernandez1]

I read up on Table normalization and realized I had a lot of work ahead of me. Meanwhile, I will act on your advice and change the query to "UNION".
The problem might continue, so I wondered about 'IF' statements like the following perhaps:

If Passenger Serial # matches across months, and, If First Name and Last Name do not match, then show results.

I know the above syntax is incorrect, but any help on this would be great!

Thanks,

[pbaldy]

Based on your description, I think you want UNION ALL, not UNION. Including "ALL" should make it include duplicates. From 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.

[Tomfernandez1]

Thanks, Paul!

[pbaldy]

No problemo!