Error message-please explain

[Michelle]

Okay, so I am pretty new to Access... I am creating a
table and I get this error message.
The changes you requested to the table were not successful
because they would create duplicate values in the index,
primary key, or relationship. Change the data in the
field or fields that contain duplicate data, remove the
index or redefine the index to permit duplicate entries
and try again.

My help command doesn't seem to be working. Can someone
give me the lowdown on what has gone wrong? I don't even
understand what this error message means much less try to
fix it.

Many thanks...

 

[Howard Brody]

Where possible, you want your tables to have a Primary Key field - a field where the value in each record is different - as a means of identifying just which employee/product/region/etc you're referring to. In employee tables, the Social Security Number is usually used instead of the name because SSN's are unique names are not (my company currently has two people, not related, with the same first and last names).

Indexes are a type of sort used in a table that makes it faster to reference (ie use in a query). Indexes can be unique or duplicate (user sets the property).

Relationships are just that - relationships - built between fields in two tables. Foe example, the SSN can be used to relate the Employees table (where the SSN is unique and the Primary Key) to a Payroll table (where the SSN may be Indexed but is not unique because it will appear in each pay cycle, which is hopefully more than once). Relationships are the reason Access is a "Relational Database" system.

The error message means you tried to make a change to a table that would result in a duplicate value somewhere that's suposed to be unique. If you were indexing a field or making it the Primary Key, then it probably contains duplicate values somewhere. I'd look at the Primary Key of the table, the field you were updating, it's indexes and any relationships the field and table have.

Hope this helps!

Howard Brody

----- Michelle wrote: -----

Okay, so I am pretty new to Access... I am creating a
table and I get this error message.
The changes you requested to the table were not successful
because they would create duplicate values in the index,
primary key, or relationship. Change the data in the
field or fields that contain duplicate data, remove the
index or redefine the index to permit duplicate entries
and try again.

My help command doesn't seem to be working. Can someone
give me the lowdown on what has gone wrong? I don't even
understand what this error message means much less try to
fix it.

Many thanks...

 

[Michelle]

Thanks!
I figured out that the primary key was the issue. I just
removed it and put it elsewhere...
You guys are very helpful!

-----Original Message-----
I have located several explanations. The main jist is

that there is duplication being represented in your
database. (submitting the same data repeatedly)

Duplicate Values
faq700-1895

If you get this error message:

"The changes you requested to the table were not

successful because they would create duplicate values in
the index, primary key, or relationship. Change the data
in the field or fields that contain duplicate data, remove
the index, or redefine the index to allow for duplicate
entries."

.... you may have a corrupted index (especially if the

database worked before without any problems).

Basic Fix:
Tools>Database Utilities>Compact and Repair

If this doesn't fix it:

Create a new blank database and import the database into it. Run Compact and repair.

If the problem continues, you may have to do a little

troubleshooting. If you know that the error message only
pops up when you add a record on this form, recreate any
queries running behind the form from scratch. If it
doesn't fix the problem, you might try recreating the form.

Microsoft also recommends deleting and recreating the

relationships when a database is corrupted.

We received this message when we were trying to create

relationships. We had a field in each table named the
same and when trying to relate the two (table1 to table2)
we saw the same message.

Hope this helps. Also try visiting microsoft.com and go

to their knowledge base under the Support tab on top right
of screen.

 

[John Vinson]

Okay, so I am pretty new to Access... I am creating a
table and I get this error message.
The changes you requested to the table were not successful
because they would create duplicate values in the index,
primary key, or relationship. Change the data in the
field or fields that contain duplicate data, remove the
index or redefine the index to permit duplicate entries
and try again.

My help command doesn't seem to be working. Can someone
give me the lowdown on what has gone wrong? I don't even
understand what this error message means much less try to
fix it.

Many thanks...

The message means just what it says. Let's pick it apart...

The changes you requested to the table were not successful
I couldn't do what you asked.

because they would create duplicate values in the index,
primary key, or relationship

You have some field defined as a Primary Key in your table. A
Primary Key must have a different value for every record in the entire
table. The record you are trying to add would put a value into this
Primary Key field that duplicates some record which is already in the
table.

Change the data in the field or fields that contain duplicate data,
remove the index or redefine the index to permit duplicate entries and
try again.

Change the data so that it doesn't duplicate existing data; or, if
you have specified a field in your Table with a unique Index, and it
doesn't need to be unique, change the Index properties in table design
view so that it's not unique.


You can also get this message if you're trying to make a field into a
primary key field, and there is already data in the table with
duplicates in that field. Use the Find Duplicates query wizard to
identify these records and fix them before setting your primary key.