limiting the number of possible entries into a composite key 1

[MrsNic]

I have a table that stores details of activities undertaken by students over a year (Maximum of 3 per year)

I have made a composite key that comprises of
studentID
ActivityID
Term
Year

This is fine, but it allows for errors e.g. a student can be entered for two different activities in the same term for the same year as this is not recorded as a duplicate. Any ideas how I can solve this? I have a feeling that it should be easy but I think I have been looking at it for too long!

Many thanks
Cath

 

[willir]

You create an index that includes multiple fields.

Open the table in design mode. Open up the Indexes window - from the menu, "View" -> "Indexes".

a student can be entered for two different activities in the same term for the same year

Create a unique index, say idxOneActivity
[tt]
Index Name Field Name Sort Order
idxOneActivity StudentID
Term
Year
[/tt]
After creating the above, select idxOneActivity again, and take note of the bottom part of the window, Index Properties. Firstly, if you do not have a primary key set for this table, and the above makes sense, then this key as the primary index. Setting this index as the primary key will set the other fields correctly.

If this not to the primary key, then set Unique to Yes. You probably want to set Ignore Nulls to No.

Richard