How can I keep a person from being assigned to a project twice

[elillis]

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.

 

[Lynn Trapp]

Create a unique index on People_ID and Project_ID (assuming these are the
names for your 2 foreign key fields)

--
Lynn 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

 

[elillis]

This allows a person to be assigned to only one project.

 

[Lynn Trapp]

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.

--
Lynn 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

 

[elillis]

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

 

[Lynn Trapp]

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.

--
Lynn 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

 

[elillis]

Thanks,
I put indexed yes with duplicates and primary keys. That works!

--Ed