Ranking of Topics in Access

[Guest]

Hi

I have a database design with three tables:

1. Student Table
2. Student/Topic table
3. Topic Table

The purpose of these tables is to allow students to pick their preferred
topics for a student project.

I need to make sure that each student only picks ten topics ranked 1 through
10 and each topic the student picks must be unique with a unique preference
(i.e they cannot allocate 1 to two different topics)

Ultimately I will be running an algorithm to allocate topics based on
average student results from the previous year.

I am using Access 2007 if that makes a difference.

Can anybody help

Thank You

 

[Allen Browne]

To prevent the student picking the same topic twice, create a unique index
on the combination of the 2 fields:

1. Open the Student/Topic table in design view.
2. Select the StudentID and TopicID fields at the same time, and mark as
primary key. (Alternatively, use the Indexes dialog to create a unique index
on the combination if you need a different primary key.)

To prevent a student picking more than 10, cancel the BeforeInsert event
procedure of the form where these entries are made.

Assuming a main form bound to the Student table, with a StudentID primary
key, and a subform bound to the Student/Topic table, the Event Procedure for
the subform would be something like this:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
Dim lngResult As Long
With Me.Parent
If .NewRecord Then
Cancel = True
MsgBox "Enter the student in the main form first."
Else
strWhere = "[StudentID] = " & ![StudentID]
lngResult = DCount("*", "[Student/Topic]", strWhere
If lngResult >= 10 Then
Cancel = True
MsgBox "No more than 10."
End If
End If
End With
End Sub