Limit to 1 record per type

[mremtff]

Hello everyone!
I am making a database that has a list of websites for each personnel file. The available types in drop-down options for each is: "personal website, professional website, and favorite website."


I want to ensure the person adding these as new records can only add each type once, and then be forced to update the one on record instead of creating duplicates. How do I do that please?

Thank you!

[Micron]

I find that confusing, but then what's new?
A person can add each type only once, thus they can have 3 records because there are 3 types. Yet you want to force them to edit the only one on record?
Wait, maybe I get it now.
They can have 3 records, but if they try to add a 4th, they cannot - they must edit one of them? And this is 3 records per person, or the one who is adding them is not related to the records, they are just doing data input. In that case, there will only be 3 records in this table.

[GinaWhipp]

Hmm, well here's a link to limit the amount of records on a continuous Form...
https://www.access-diva.com/vba5.html

Just modify it so it allows each User to add three records. Hmm, come to think of it if you want them to only select one of the type of website you will also have to limit that selection on their User ID as well making sure to limit the combo box to only selections not made.

[CJ_London]

an easier solution might be to denormalise a bit and have one record with three fields - personal, professional, favorite

[accesstos]

Or, you can define a composite primary key with [Personal ID] and [TypeID] for the "PersonWebSites" table.
You can edit/delete the websites of a person with the types that already exists in the table and append only those types that doesn't.
If you add a fourth available type, the composite key will allow four records for each person, and so on.

[mremtff]

Okay let me clarify a bit. I'm actually not super sure how to ask the question which is why I've had a hard time trying to search for an answer that may have been posted.

Say I have a table where I have the following columns:
ID, UserID, PhoneNumber, PhoneType

ID is autonumber, UserID is linked to a personal profile, PhoneNumber is in phone format, and PhoneType is a lookup with 3 options (Work, Home, Mobile)

I want to be able to input only ONE of each type, (Work, Home, Mobile) for each UserID. I don't want someone using the database to be able to enter 4 different Work numbers, 2 Home numbers, and 5 Mobiles. I want to limit each PhoneType to a max of 1 per type.

I have a novice level of Access, so I'm sorry if I don't quite understand all your responses. But I appreciate the help!

[Micron]

Then I would have a table as you describe here where the userID and type are a composite index as was suggested. The settings would allow 1 type and 1 number per user, thereby also limiting them to no more than 3 numbers. As was stated, if you add a type, it will automatically be able to become part of the user records because adding this new type won't violate the composite index. Records would resemble

UsrNumID	UserID	PhoneNum	PhoneType
1	1	123-456-7898	mobile
2	1	128-458-7588	home
3	1	145-569-7858	office
4	2	568-852-7852	home
5	2	568-784-9515	mobile
6	3	568-741-5574	home

user 1 would not be able to add a record because it would duplicate his ID and one of the types.

[mremtff]

Solved! Thank you all for the help.

I did make the primary key out of both of the CandID and Type fields.
I didn't even realize I could do that.

Thank you all!

[Micron]

A composite PK and a composite index (which I thought is what was suggested) are not the same thing. Which one did you create?
Some would say to create a composite index and don't use composite PK's.