Find Duplicate PrimaryKey Violation... (1 Viewer)

[JaedenRuiner]

Okay,

I have 33,099 records in a query, that i'm importing into a table. (don't bother witht he semantics, it's from a linked dbf file)
The table does not have a primary key. Given Three Fields (out of 74):

Item_ID
Title
AltTitle

With the table populated with all the records, I highlighted those three fields in Design View of the table, and told told access to make all three of them the Primary key. Upon attempting to save the table, I got an error message saying that data in the table violated the primary key unique fields rule or what not.
So I wanted to make a query to determine where the error occurred. I could not off the top of my head figure out how to select only the duplicated records in a table, so instead, i figured if they violated the Primary Key unique field rule, there should be duplicate entries. so I did this:

select distinctrow item_id, title, alttitle from tbl_Table;

I got 33, 099 records returned on the DISTINCTROW. Strange as that was, I deleted all the records from the table, set the primary key as I wanted it, and then repopulated the table via my sql insert into commands. This time the table reports only 33,093 records, meaning 6 records somehow violate the primary key unique index, but don't violate a DISTINCTROW call. How can i find them to determine how they are violating the primary key unique index?
thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

 

[Len Boorman]

Try

select distinct item_id, title, alttitle from tbl_Table;

This should give you the 33093 non duplicated PK fields

need to think on how you would use this to find the 6 oddballs

L

 

[ejstefl]

What you can do is this:

1. Create a copy of the table (structure only, no data)
2. Set the primary key on the new table
3. Append data from the old table to the new. Only 33,093 fields should append.
4. Do an Unmatched query on the old and new tables. This should report the 6 problem records.

 

[Pat Hartman]

Use the find duplicates query that the wizard creates.
Open the query section of the database container.
Choose New.
Choose the find duplicates query and let the wizard build what you need.