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
|
|||
|
|||
Table relationships, queries, forms and reports...
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 |
#2
|
|||
|
|||
Table relationships, queries, forms and reports...
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "KarenF" wrote in message ... 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 |
#3
|
|||
|
|||
Table relationships, queries, forms and reports...
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 "Allen Browne" wrote: 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. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "KarenF" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|