Table relationships, queries, forms and reports...

[KarenF]

Hi,

I'm creating a database for someone who requires a timetable of
interpreters/note takers and helpers for disabled students attending classes
at college. I have most of this working; however, the difficulty comes when
I need to assign more than one interpreter to a student's session (which is
every time), or more than one note taker.

I have a master Interpreter table showing id, first name, last name; another
table showing id and hours of work. I have the same for note takers, with
the addition of another table showing id and subjects they take notes for.

I've created a timetable table which I'm convinced is not right. I have a
class id (which comes from a separate table showing module name, day, time,
tutor, venue etc), the student id, interpreter 1 (lookup from interpreter
table), interpreter 2 (lookup from interpreter table), remote note taker
(lookup from note taker table), electronic note taker (lookup from note taker
table), and note taker (lookup from note taker table).

I'm thinking I really ought to have a separate record for each separate
interpreter/notetaker for each session. The person I'm creating this for is
already unhappy that she's having to populate the timetable table for each
student for each class they take every week.

The end result she needs to see is a timetable for each interpreter or note
taker showing each session, venue, time etc they need to attend. The
interpreter would also need to see the name of the second interpreter working
with them (same ofr any note takers). This will be in a report.

I've successfully created queries for this - of course, I can use the QBE
grid and set or criteria. However, I've tried to create a main interpreter
form with a timetable subform, which works great when the interpreter's name
is in the Interpreter 1 column, but ignores the link with the interpreter 2
column (as I thought it would).

Any advice would be gratefully received. I've created many databases
before, but none have been as challenging as this one. When I was given this
task to do, all of the information was in 1 table and columns were hidden and
displayed in queries to provide the information they required. So, the
database owner is used to that format and can't grasp the concept of
normalisation and has never used forms - never mind forms and subforms.

I think I've been looking at this for too long, so if anyone can point me in
the right direction, I'd be really grateful.

Thanks a lot.
Karen

 

[Allen Browne]

Karen, let me suggest that you put all the people into one table, regardless
if whether they are students, interpreters or notetakers.

You can then create 4 tables like this:
tblPerson: one record for each person, with PersonID primary key.

tblSession: one record for each session, with SessionID primary key

tblSessionPerson, with fields:
- SessionID: relates to tblSession.SessionID
- PersonID: relates to tblPerson.PersonID
- RoleID: relates to tblRole.RoleID

tblRole: one record for each type of role.

Now if you need to assign a student, and 2 interpreters to session 24, the
record in tblSessionPerson would look like this:
SessionID PersonID RoleID
24 98 student
24 14 interpreter
24 123 interpreter
As you can see, you can put any combination of people into a session using
this design.

There's one more consideration here. If a person can have only one role
(they could never be a notetaker and also an interpreter or student), your
tblPerson can have a RoleID field to indicate that person's role. However,
if there could be cases where a person has mulitple roles, you need one more
table - tblPersonRole - with fields like this:
- PersonID who has this role
- RoleID the role this person has.
So if a person has 2 roles, they have 2 entries in this table.

 

[KarenF]

Hi Allen,

Many thanks for your help. These are the fields I am required to work with:

Student Added (tick box), Forename, surname, Interpreter, Module Code,
Module Title, Module Description, Tutor, Tutor Email, Tutor Contact, Tutor
Room No, Semester, Week Number (when I was presented with this database to
work on there was 1 large table and a field for each week number and a notes
field for each week number), Time table given by student, Class Day, Start,
Finish, Hours, Venue, Interpreter 1, Interpreter 2, Electronic notetaker,
Remote notetaker, Manual Notetaker, Comments, Change of Venue, Change of
time.

I have included each student's unique id and given interpreter, note takers
and tutors ids also. I've created a table called classes which provides a
unique class id, module id (from a table which I've called Module which
includes the Module Code, Title, Description etc), tutor id (from a table
I've called Tutors), Day, Start Time, Finish Time, Venue.

I'm using a table (timetable) to place each student on each class, but
because we need to see any weekly changes, my colleague is going to have to
enter this information for each class for each student for each week. It is
this table in which I'm assigning Interpreters and Note takers. I've created
a form to enable data entry. I am able to create reports for timetables for
each interpreter, but can't show the interpreter timetables as a subform of
the interpreter form.

Thanks again for your help Allen. I'm going to try re-jigging this. My
colleague is now asking for a quote in terms of time and expense for doing
this. Wish me luck!

Take care,
Kind regards,
Karen