Primary Key

[DPThomas]

I am wondering if there is a way to avoid entering a record if 2 fields are the same rather than one. ie Item Number (can contain no duplicate) Description can contain duplicates but not 2 with the same item #. This may seem rather elementary, but I can't seem to come up with a way to avoid this problem

In actuality here is what it would look like for our uses
set # Style
2645 8783-61
2645 8783-42
2645 8783-43
2645 8783-61

I do not want to allow that last record to occur... Any Help would be greatly appreciated.

Thanks
Dave Thomas

 

[DPThomas]

You need to use what is called a compound primary key. To do this, open the table in design view. Click to highlight the first key field, then while holding the control key, click to highlight the second key field. Press the key icon on the tool bar to create the primary key. You can select as many as 10 individual fields to combine as one compound primary key or index.

To view all indexes and the primary key, press the lighning tool on the toolbar.

Thanks alot for your time, this is the greatest Access forum I have ever run across. In all the classes I have attended on Access no one has ever mentioned this little solution

Thanks Again

 

[AN60]

PH
Can a compound primary key be created after a db has been created and contains data without much fuss? It sounds ideal for one of my applications where I already have a primary key field but want to prevent duplicates in a tax file field within the same tbl.

 

[AN60]

PH
Your suggestion seems to work well and I have already implemented it. One or two fields are unable to be selected and they do not contain duplicates, any ideas on that one? (access2000)

I like the idea because my earlier db's use many combo's based on text to look up data and at least I can prevent duplication. Although I've had no problems I wish I'd used the primary key when I created them (didn't know then).

Thank you for your assistance.

 

[AN60]

PH
I think it might be my error. I just discovered that the shortcut I had been using to start one of my access 2000 db's, which is the one in question, was pointed to my "old access97" backup with the same name. As I have not opened the 2000 or 97 since converting to 2000 I hadn't noticed that I was in 97. Access 2000 will not allow saving anything still in 97 format. Thank you for your help.