 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Database Design / July 2005How can I keep a person from being assigned to a project twice
I have 3 tables: People, Projects, People\Projects. Table People\Projects kepts track of the people assigned to a project. How can I keep a person from being assigned to a project twice. Create a unique index on People_ID and Project_ID (assuming these are the names for your 2 foreign key fields)  SignatureLynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Access Junkie List: http://home.bendbroadband.com/conradsystems/accessjunkie.html >I have 3 tables: People, Projects, People\Projects. > Table People\Projects kepts track of the people assigned to a project. How > can I keep a person from being assigned to a project twice. This allows a person to be assigned to only one project. > Create a unique index on People_ID and Project_ID (assuming these are the > names for your 2 foreign key fields) > >>I have 3 tables: People, Projects, People\Projects. >> Table People\Projects kepts track of the people assigned to a project. >> How can I keep a person from being assigned to a project twice. It shouldn't, if it is a compound unique index. Under the situation I described you should be able to see something like this in your People_Projects table People_ID Project_ID 1 1 2 1 1 2 2 2 2 3 All of those are unique records and, as you can see, People_ID 1 is tied to 2 projects and People_ID 2 is tied to 3 projects. SignatureLynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Access Junkie List: http://home.bendbroadband.com/conradsystems/accessjunkie.html > This allows a person to be assigned to only one project. > [quoted text clipped - 4 lines] >>> Table People\Projects kepts track of the people assigned to a project. >>> How can I keep a person from being assigned to a project twice. I must be doing something wrong. I get a duplicate indication error when I try to enter the second record. I put yes (no duplicates) in thje indexed field. --Ed > It shouldn't, if it is a compound unique index. Under the situation I > described you should be able to see something like this in your [quoted text clipped - 18 lines] >>>> Table People\Projects kepts track of the people assigned to a project. >>>> How can I keep a person from being assigned to a project twice. I think the problem is that you do not have a compound index. You need the unique index to be against both fields at the same time. The easiest way to accomplish that is to make the 2 fields a compound primary key. SignatureLynn Trapp MS Access MVP www.ltcomputerdesigns.com Access Security: www.ltcomputerdesigns.com/Security.htm Jeff Conrad's Access Junkie List: http://home.bendbroadband.com/conradsystems/accessjunkie.html >I must be doing something wrong. > I get a duplicate indication error when I try to enter the second record. [quoted text clipped - 24 lines] >>>>> Table People\Projects kepts track of the people assigned to a project. >>>>> How can I keep a person from being assigned to a project twice. Thanks, I put indexed yes with duplicates and primary keys. That works! --Ed >I think the problem is that you do not have a compound index. You need the >unique index to be against both fields at the same time. The easiest way [quoted text clipped - 29 lines] >>>>>> project. How can I keep a person from being assigned to a project >>>>>> twice. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.