Confused with Relationships

TaiChi56

Registered User.
Local time
Yesterday, 16:14
Joined
Nov 4, 2004
Messages
22
I am very new at doing Access database. I have read stuff on it but I just keep confusing myself. I am trying to build a master database that holds all information on our teachers. Such as staff development workshops attended, keys that have been issued to them and so on.

I have three tables. One has the teachers information such as name, ss#, address etc... The other table will have the staff development title and dates and the third table will be a key database.

I want to be able to open the database in a switchboard, which I have done before and be able to add information in a particular teacher's file such as keys issued or returned, staff workshop just attended.

I have attached a sample of what I have started. Could you lead me in the right direction? Thank you.
 

Attachments

TaiChi56 said:
I am very new at doing Access database. I have read stuff on it but I just keep confusing myself. I am trying to build a master database that holds all information on our teachers. Such as staff development workshops attended, keys that have been issued to them and so on.

I have three tables. One has the teachers information such as name, ss#, address etc... The other table will have the staff development title and dates and the third table will be a key database.

I want to be able to open the database in a switchboard, which I have done before and be able to add information in a particular teacher's file such as keys issued or returned, staff workshop just attended.

I have attached a sample of what I have started. Could you lead me in the right direction? Thank you.

The relations are easy. Each teacher record has a primary key (I would use an autonumber instead of SS#, whihc I see you did). This primary key value becomes a foreign key in the child tables. The Teacher table is the parent and the other two are the children. To enter data you would use a main/sub form combination. Your main form is bound to the Teacher table and your subforms to the child tables linked on the key value.

Looking at your sample. I'm not sure what productID is in your tblKeys. You should have an autonumber KeyID and then the TeacherID as a foreign key. If the Roomkeyassigned field is supposed to indicate that its assigned, that's unnecessary. If the TeacherID is null then the key wasn't assigned. Otherwise it was.

I'm not sure I understand what the tblStaffDev fields do, but you need to add a PK and the TeacherID as a FK to it.
 
Last edited:
You have your data tables set, now you just need tables to capture the data.

Attached is your DB with a thrid table "TeacherKey_index" which has 2 primary keys. TeacherID and KeyID. This table will hold your data.
 

Attachments

Makes sense now

AndyVM said:
You have your data tables set, now you just need tables to capture the data.

Attached is your DB with a thrid table "TeacherKey_index" which has 2 primary keys. TeacherID and KeyID. This table will hold your data.


Thank you, it makes sense now.
 
I have a few questions, in addition to those Scott raised about "development days".

First, are you at all concerned about history of teacher's class and department assignments? In other words, are you interested in tracking which class/department assignments have had during their tenure on your staff, or are you interested only in current assignments without regard to what they have done previously? If a teacher changes classes during the school year, does it matter that you can't show the teacher was in one class for part of the year and in a different class for part of the year? Or that you don't know when the change occurs, if it does?

Second, are you interested in the dates when teachers join or leave your staff? Or are you only concerned with the fact that they are, or have been on your staff at any given time? If a teacher leaves your staff, how do you show that they are no longer with you, but that they were there for a period of time?

Third, are you interested in the dates when teachers are given keys, or the dates when they returned them? Or are you only interested in knowing that teachers have now, or have had at some point, possession of a given key? Would it matter if a teacher left your staff that they still had one or more keys checked out? How would you know that, if it is important?

Can two teachers have possession of a key at the same time? (I think I know the answer to that one.) If a teacher turns in a key, how do you show that it is available to be checked out by a different teacher? In the event it turns up missing, and it is not currently shown in the key_teacher table as being checked out, how do you know who had it last?

You've got a decent start, but the absence of dates will probably prove to be a challenge.
 

Users who are viewing this thread

Back
Top Bottom