Hello,
I am a tutor at a university writing lab and have volunteered to work on designing a database to bring our current paper-based scheduling system into the 21st (or 20th ?) century. I worked on access extensively a few years ago but haven't picked it up in a while; nor do I have a great deal of experience with the kinds of forms or logic this might require.
In any event, I'm anticipating that I would need tables and fields for:
TUTORS
Name
Contact Info
Hours Scheduled to Work
Available Hours
STUDENTS
Name
Student ID
Phone
Primary Class [that brought them in to the center]
Additional Field indicating if they are in certain kinds of classes that require particular minimum # of appointments per semester (remedial and writing-intensive courses)
Appointments attended
Appointments missed
SCHEDULE
Half-hour appointment slots
I have a feeling that this hypothetical design is rife with the kinds of overlaps that would undermine a relational design. The database I'm thinking of would need to be able to do things like have a form for scheduling that populates a calendar with as many open half-hour time slots as we have tutored scheduled to work for that time; that would have sufficient logic to prevent scheduling students who have missed two appointments without administrator approval; that would (if possible) generate e-mail reminders, or at least lists of e-mails of students who have appointments coming up in 24 hours or less ... and a few other features.
I realize some of this may have to do with access features and not with database design itself, but I'm hoping someone can give me a sense of whether access is the appropriate solution for our needs, and how I might want to get started. Thanks,
Prawer