multiple relationships with one field

[Guest]

hi
i have a database set up with a table for monitoring registarations for
seminars each attendee can attendee up to three seminars and as such i have
three fields in this table (amoung other) SeminarId1, SeminarID2, SeminarID3.
Each one of these has a one to many realationship with the primary key of the
seminar table SeminarId. On the form i wish to have the name of the seminars
apear next to each of these when the id is entered. This always works for me
when the object is in one realationship yet i can only work out how to get
one seminar textbox that shows the data of SeminarId1 (using the wizard) yet
cannot work out how to do this for this situation.
Thanks in advance
Rowan

p.s is it also possible to restrict the amount of times a certain piece of
data can be entered in a field (each seminar can only take 30 people) so it
would be nice if after the field contains 30 entries for seminar 1 he input
mask changed to no longer accept this value,
i am confident with vb and am sure this would need to be done in code just
wondering if there is an easier way.

 

[Allen Browne]

Rowan, you need a different table design.

Attendee table: one record for each person.
AttendeeID AutoNumber primary key
Surname Text
FirstName Text
...

Seminar table: one record for each seminar.
SeminarID AutoNumber primary key
SeminarName Text
SeminarDate Date/Time

SeminarAttendee table: one record for each combination of Attendee and
Seminar (i.e. 3 records if one person is attending 3 seminars):
SeminarID Number Relates to Seminar.SeminarID
AttendeeID Number Relates to Attendee.AttendeeID
(You can use the combination of SeminarID + AttendeeID as primary key, to
prevent one person being enrolled in the same seminar twice.)

The 3rd table is called a junction table, and it is the standard way of
resolving a many-to-many relation. Repeating fields (such as Seminar1,
Seminar2, ... ) are a major No-No in relational design.

There is another example of this in this article:
Relationships between Tables
at:
http://allenbrowne.com/casu-06.html


Once you have that sorted out, with the main form for the Seminar, and the
subform bound to SeminarAttendee, you can prevent too many people attending
the seminar by cancelling the BeforeInsert event of the subform if there are
already 30 enrolled.

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim strWhere As String
Dim lngCount As Long

If Me.Parent.NewRecord Then
Cancel = True
MsgBox "Enter the seminar in the main form first."
Else
strWhere = "SeminarID = " & Me.Parent!SeminarID
lngCount = DCount("*", "SeminarAttendee", strWhere))
If lngCount >= 30 Then
Cancel = True
MsgBox "No more than 30 permitted."
End If
End If
End Sub

 

[peregenem]

Attendee table: one record for each person.
AttendeeID AutoNumber primary key
Surname Text
FirstName Text
...

Seminar table: one record for each seminar.
SeminarID AutoNumber primary key
SeminarName Text
SeminarDate Date/Time

SeminarAttendee table: one record for each combination of Attendee and
Seminar (i.e. 3 records if one person is attending 3 seminars):
SeminarID Number Relates to Seminar.SeminarID
AttendeeID Number Relates to Attendee.AttendeeID
(You can use the combination of SeminarID + AttendeeID as primary key, to
prevent one person being enrolled in the same seminar twice.)

Allen, You've omitted a couple of constraints i.e. three seminars per
person (I've unilaterally decided they are students <g>) and 30 people
per seminar. Here's a suggested design which includes these
constraints:

CREATE TABLE Students (
student_ID INTEGER NOT NULL PRIMARY KEY);

CREATE TABLE Seminars (
seminar_ID INTEGER NOT NULL PRIMARY KEY);

CREATE TABLE SeminarPlaces (
seminar_ID INTEGER NOT NULL
REFERENCES Seminars (seminar_ID)
ON DELETE CASCADE
ON UPDATE CASCADE,
place_nbr INTEGER NOT NULL,
CHECK (place_nbr BETWEEN 1 AND 30),
PRIMARY KEY (seminar_ID, place_nbr));

CREATE TABLE StudentSeminarPlaces (
student_id INTEGER NOT NULL
REFERENCES Students (student_ID)
ON DELETE CASCADE
ON UPDATE CASCADE,
seminar_ID INTEGER NOT NULL,
UNIQUE (student_id, seminar_ID),
place_nbr INTEGER NOT NULL,
UNIQUE (seminar_ID, place_nbr),
FOREIGN KEY (seminar_ID, place_nbr)
REFERENCES SeminarPlaces (seminar_ID, place_nbr)
ON DELETE CASCADE
ON UPDATE CASCADE,
student_seminar_nbr INTEGER NOT NULL,
CHECK(student_seminar_nbr BETWEEN 1 AND 30),
UNIQUE (student_id, student_seminar_nbr),
PRIMARY KEY (student_id, seminar_ID, place_nbr));

The next step would be to create a VIEW of available seminar places...