Changing Primary key to new field

[Kemidan2014]

I know the steps on changing the primary key, however I am having trouble with Backend accepting the new primary key

Changing the Index to Yes (No duplicates) i get the "The changes you made were not successful, doing this will create duplicates...etc" Error
This field is a Short Text field
i ran a duplicate query and confirmed there are NO duplicates.

The only other options the error suggests to change are not in the spirit in the direction i want to go, because it is telling me to change Index property back to No or Yes WITH dupes...

So i feel like this is another Error message that does not have all the information inside the message to direct you as to what could be the problem.

are there any other reasons why a short text field with no duplicate values in it would be getting prevented to be selected as primary key?

 

[Micron]

i ran a duplicate query and confirmed there are NO duplicates.

No disrespect intended, but I'd wager that you are incorrect. If the field contains records with empty strings then it only takes 2 such records to create this issue. One thing you can do is create a query that searches these fields for zls (zero length strings) with criteria such as ="" . If the query returns any rows, you have records with zls' in them. Or just look at the properties of the fields to see if allow zero length is true. If not, that cannot be the problem.
I have never seen this message to be wrong, so I'd suggest you take a very close look if none of that helps. Or post a copy of a db with that table and others may take a look for any dupes.

 

[welshgasman]

Try running a group by query with count.
Then see what is produced.

 

[Joe4]

Just to reiterate what Micron said, make sure that you do not have any blank values in that field.
Outside of potential dups because you have some blanks, you also cannot have blank values in a primary key field.
So you would need to clean up those values first.

 

[Kemidan2014]

-,- Micron you are Correct. I discovered my original Duplicate Query set up was incorrect which led me to think I had none (but i have no ZLS thank fully).

All of these are related to REALLY old complaints where our customers system did not include individual customer plant ID with in the [QIMS#] so evidently it made it possible for them to create dupes for this field but if you look at the rest of the fields of data along the duplicate records there is differing information.

The excel linked table we use to download data into the access database contains a field called "Instance key". looking at this field among all the duplicates. the values are different so I think my problem will be solved by switching primary key to this instance key field AFTER i confirm No blanks and No dupes. My ignorance for only focusing on the data structure of the most recent info when building this database.

 

[Micron]

Another option would be to create a compound index of 2 or more fields that would assure no dupes. Then you could have
A | A
A | B
A | C
B | A
B | B
B | C

but not A | A again.