I need some help with the design of a system I'm building to track Position Appointments.... I'm using Access 2007.
Some Background:
I have 5 tables:
Members, positions, appointments, districts, conference schedule
My appointments table has foreign keys to members and positions as well as a start and end date.
I want to be able to track when a person was assigned to a position and when they were unassigned. The start date and end date in the appointments table should handle that.
Appointments are made at various conferences held during the year. 12 conferences occur once a year in various locations. A conference may handle appointments for up to 5 districts.
I have to maintain a schedule of these conferences – I’ve worked that out with a conference schedule table that contains the conference name, conference begin & end dates, and conference location.
I have a conference definition table that links which districts are assigned to each conference.
I have to track the appointments made for each district. – the appointments table also has a FK to the district.
Most positions are the same between the 12 conferences – but some may have positions that the other conferences don’t offer.
More than 20 appointments are made for each district at each conference.
Appointments can also be made in the interim of these conferences.
Members can be re-appointed to the same position each year (which happens the majority of time).
Design Questions:
How to handle the user interface for maintaining the appointment information?
Should I have an assignment table and a transaction table to track the type of appointment being made?
I don’t think I want multiple appointment records for all of the members who have been in a position for a long time – I don’t really want the user to enter a record every year for all those people ….. yet, I need to record the fact that a re-appointment happened for that member in a particular conference…..
I can handle an interim appointment by using an appointment form in single form view. But, I want to use datasheet view when the user is entering data from the results of a conference.
Most members that are appointed in the interim of a conference, receive a regular appointment at the next conference.
Can you help me find some table and/or form examples of some business case similar to this one that may inspire me? I’ve been looking and can’t find anything. If I can see a form, I can probably figure out how to design the underlying tables.
Any help will be appreciated.
Some Background:
I have 5 tables:
Members, positions, appointments, districts, conference schedule
My appointments table has foreign keys to members and positions as well as a start and end date.
I want to be able to track when a person was assigned to a position and when they were unassigned. The start date and end date in the appointments table should handle that.
Appointments are made at various conferences held during the year. 12 conferences occur once a year in various locations. A conference may handle appointments for up to 5 districts.
I have to maintain a schedule of these conferences – I’ve worked that out with a conference schedule table that contains the conference name, conference begin & end dates, and conference location.
I have a conference definition table that links which districts are assigned to each conference.
I have to track the appointments made for each district. – the appointments table also has a FK to the district.
Most positions are the same between the 12 conferences – but some may have positions that the other conferences don’t offer.
More than 20 appointments are made for each district at each conference.
Appointments can also be made in the interim of these conferences.
Members can be re-appointed to the same position each year (which happens the majority of time).
Design Questions:
How to handle the user interface for maintaining the appointment information?
Should I have an assignment table and a transaction table to track the type of appointment being made?
I don’t think I want multiple appointment records for all of the members who have been in a position for a long time – I don’t really want the user to enter a record every year for all those people ….. yet, I need to record the fact that a re-appointment happened for that member in a particular conference…..
I can handle an interim appointment by using an appointment form in single form view. But, I want to use datasheet view when the user is entering data from the results of a conference.
Most members that are appointed in the interim of a conference, receive a regular appointment at the next conference.
Can you help me find some table and/or form examples of some business case similar to this one that may inspire me? I’ve been looking and can’t find anything. If I can see a form, I can probably figure out how to design the underlying tables.
Any help will be appreciated.
