Out of Control Combo Boxes

[Dan M]

I organize volunteers for a series of 10 events throughout the course of a
year. Since each volunteer can attend multiple events, and each event has
multiple volunteers, I figured out I shoudl be using a junction table. This
table contains the fields "VolunteerID" (a combo box that links to the
volunteer table) and "EventID" (linked to the event table)

When I made the form for the volunters, I included this junction table.
However, when I add new events to a volunteers, I get a list that is 10
events for each year. Since we have 10 years of data, we get somethign that
looks like:
Event1 2000
Event1 2001
Event1 2002
....
Event1 2010
Event2 2000
Event2 2001 and so on...

So we end up with a list has 100 entries, one for each row on the events
table. This isn't so much a problem in the events table, but seems
needlessly messy in the form. Is there a way that this one field could be
split into two; one listing the 10 events, and one listing the year, and
still have it link back to the relevant entry in the events table?

Thanks!

 

[Ken Sheridan]

If the set of 10 events is always the same each year then you only need 10
rows in the Events table, one for each. The EventYear is an attribute of the
relationship between Volunteers and Events, which your Attendances 'junction'
table is modelling, so should be a column in that table. If Volunteer 42
attends Event 3 in 2009 there would then be a row in this table:

42 3 2009

The model would thus be:

Volunteers---<Attendances>---Events

If, on the other hand, the events vary from year to year then you will need
multiple rows in the Events table, one for each year an event is held. Note
that the EventID value will be repeated for each event of the same type, but
with a different EventYear value for each and this table will not now include
the event names. The Attendances table will be unaltered, still needing the
EventYear column as the relationship between it and Events is now on the
composite keys of EventID and EventYear. You will also need another table
EventTypes say, with one row per event with EventID and Event columns. This
is related to Events on EventID. The model would thus look like this:

Volunteers---<Attendances>---Events>---EventTypes

where the < and > signs denote the 'many' end of each relationship type.
Referential integrity should be enforced in each relationship of course.

For data entry, in either of the above scenarios, an appropriate set-up
would be a Volunteers form with an Attendances subform. In the parent
Volunteers form add an unbound combo box, cboYear say, which lists all years
over a suitable period from some year in the past to one in the future. You
can then link the form ands subform by setting the LinkMasterFields property
to VolunteerID;cboYear, and its LinkChildFields property to VolunteerID,
EventYear. This will show all events for the selected year which the
volunteer is signed up for. New events to be attended by the volunteer in
the currently selected year can be entered in the subform of course.

Conversely you could have an Events subform containing a similar subform,
but this time setting the LinkMasterFields property to Event;cboYear, and its
LinkChildFields property to EventID, EventYear. This will show all
volunteers signed up for the current event in the currently selected year,
and once again new volunteers for that event/year can be added.

Ken Sheridan
Stafford, England