Trying to eliminate duplicates

[m_ehart]

I have a database that has over a million records in it and I am trying to get rid of any duplicate entries in this database. I wanted to make the two fields I am trying to make unique, primary keys but can I make these two field primary keys if the database already exists with duplicate records in it. If so how? I would greatly appreciate any help you could give me. When I try to make the two fields a primary key it tells me that duplicate data already exists and I need to modify my database. Please help I am struggling to meet my deadline!!!! Thanks

 

[RichMorrison]

Amanda,

Access is telling you the truth. You can't make a primary key from one, two, or ten fields unless the combination produces a unique value for each and every record.

RichM

 

[m_ehart]

So do you have any suggestions on how I can eliminate duplicates that already exist in the database, by searching for any duplicates that exist based on the two fields I want to be my primary key. Because if I can get rid of the duplicate data then I could probably go ahead and make these two fields my primary key.

 

[RichMorrison]

Amanda,

Try this:
1) Make a "totals" query (the sigma sign in the toolbar)
2) Select your first field as a Group By
3) Select your second field as a Group By
4) Make a third field that is Count(*). It is an Expression
In the criteria cell under Count, enter >1

Run the query. You will get a list of all combinations that have more that one occurrence.

RichM

 

[georget]

Pat,

This is a simple & awesome solution. Thanks!!!