Help with select query

[albert1992]

Hello,

I have two tables, the first with policy numbers without duplicates, the second table contains some of these policy numbers but with duplicates. I am trying to create a new table with only the number of records in the first table while grabbing some fields from the second table.

I've tried the second option in the access query join properties but I'm guessing it creates duplicates.
Is there a criteria for this to have unique values?

 

[Micron]

Is there a criteria for this to have unique values?

Yes, but the simple answer may not be the solution. What I don't get is whether or not ALL of the fields are exactly the same in the records where the duplicate policy numbers exist. Here's the answer to your question anyway. Right click on the query background where the tables a shown. Bring up the property sheet and F1 on each of these properties - Unique Values and Unique Records. The explanations should help you figure out which setting you need (you cannot have both). Depending on what you're working with, you may have to create the table you need and append from the one with the dupes, having the first table policy number field set to be a unique index. The duplicate records would not get appended. That of course, depends on what I said about the other fields. I have no idea how you plan to decide to take some of the fields from duplicate records. 3 from the first and 2 more from the second duplicate? 5 from one and skip the other? Not clear.