If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Out of Control Combo Boxes
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! |
#2
|
|||
|
|||
Out of Control Combo Boxes
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 "Dan M" wrote: 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! |
Thread Tools | |
Display Modes | |
|
|