ms access primary key and duplicates

M

Maria

Aloha all! I hope somebody can help me. I created an MS Access database
originally with an ITEMID as the primary key, meant to be unique and thus
should not accept duplicate records. I started inputting data and it worked
well until I found out later that the ITEMIDs assigned to the records were
not unique after all. I edited the database to instead use three fields:
ITEMID, NEWSTYPE and YEAR as the primary keys and hoped that with these
fields, I would have created a unique ID but would accept similar ITEMIDs for
different NEWSTYPE and YEAR. The database still flags a duplicate ITEMID even
with different NEWSTYPE and YEAR.

Any suggestions on how I can resolve this? Many thanks in advance.
 
T

Tom Wickerath

Hi Maria,
I edited the database to instead use three fields:
ITEMID, NEWSTYPE and YEAR as the primary keys...
Click to expand...

A table can have only one primary key. A primary key can be a combined field
primary key. When you did this, did you see the key symbol next to each field
name?
The database still flags a duplicate ITEMID even
with different NEWSTYPE and YEAR.
Click to expand...

You might have a unique index remaining on the ITEMID field. Open the table
in design view. Then click on View > Indexes. Verify that you do not have a
single field index with Unique set to Yes for this field. A single field
index includes an index name and only one field before the next index name is
shown.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Aloha all! I hope somebody can help me. I created an MS Access database
originally with an ITEMID as the primary key, meant to be unique and thus
should not accept duplicate records. I started inputting data and it worked
well until I found out later that the ITEMIDs assigned to the records were
not unique after all. I edited the database to instead use three fields:
ITEMID, NEWSTYPE and YEAR as the primary keys and hoped that with these
fields, I would have created a unique ID but would accept similar ITEMIDs for
different NEWSTYPE and YEAR. The database still flags a duplicate ITEMID even
with different NEWSTYPE and YEAR.

Any suggestions on how I can resolve this? Many thanks in advance.
 
M

Maria

Gee, thanks Tom. All three fields did have the primary key symbol. Earlier, I
tinkered with Unique but was afraid it would mess up my database. I now
changed Unique to accept duplicates and it works.

Happy New Year.

Maria
 
T

Tom Wickerath

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

primary key - concatenated or autonumber? 6
Trying to create a report that will list unmatched records by Ticket Primary Key 0
Update primary Key. 1
No Primary Key! 4
Preventing duplicate records using two and three fields? 8
Autonumber trying to create duplicates 3
Adding a primary key 3
Primary Key reverted to earlier number 5

Top