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
|
|||
|
|||
Still Struggling...
Hi,
I'm still struggling to organize my tables and fields but I'm not as *totally confused* as my original post; at least I hope I'm not. I'm new to Access. This is my first database and I fluctuate between feeling hopeful I can do this one minute and despairing that I can't the next because I've thought of yet another complication that I don't know how to handle. I have scoured the Internet and this disscussion group searching for the answers that I need. I found some but I am still at a loss as to what to do about others. I was wondering if someone would be kind enough to review my table structure and respond to some questions at the end. I am truly grateful. The information is as follows: tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text tblKeysEmployees Key ID- composite key(FK to tblKeys)- text EmployeeID-composite key (FK to tblEmployees)- Autonumber tblKeysRequests Request ID-Autonumber Key ID (FK to tblKeys)- text Emp. ID Rm. number- text--I think there is a problem here. This info is part of tblrooms. Rm. phone- text Date Requested- Date/time Date Recvd.- Date/time Date Issued- Date/time tblRooms Key ID (PK)-text Room number- text Remarks-text tblSubs Sub ID (PK)-Autonumber SubLN-text SubFN-text MI-text SubPhone-text Key ID (FK to tblKeys) Date Issued-Date/Time Date Returned-Date/Time If you're still reading, I have the following questions: 1. How many fields are too many in a table? I understand that Access will accept up to 255. I read a post that suggested that 20-30 fields may indicate there may be a normalization problem. I'm wondering about tblEmployees where there are approx. 25 fields. 2.tblRooms has a field for Rm.#; the problem is that not all rooms have a #. Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks to tblRooms because I may have to describe a location rather than a room #. Will there be a problem with this method? One of the desired reports is a reverse directory by room #/room phone #. The phone # is currently in tblEmployees. Should it be moved to tblRooms? If I leave it where it is, will I be able to update any queries and reports? I've read that multitable queries aren't updateable. We have had mass staff room changes right before the school year begins. It just seems it would be better if the phone # was listed with tblRooms but someone told me that's incorrect. 3. I forgot to include the following fields: Date assigned, Return Date and Permission to Retain (over the summer) for Emp. Keys. Should this be added to tblKeys or tblEmployees? I was told not to put *anything* into the junction table (tblKeysEmployees) besides the composite key already listed. 4.Keys Requests--Somethimes are request will not have a name associated with it. For instance, we need to request a key for the vault. I have not accounted for this situation and don't know how to handle it. If I need to order a sub. key, I can use the name of the permanent staff member associated with that room. What do I do about the vault? Should I enter "vault" in the emp. table w/o any additional data? 5. Maybe I should cross this bridge when I get to it but I eventually will get to the point where I will make forms and subforms. There are approx. 400 seperate keys, not including multiple copies of the same key. If I have a combo box, is there a way to filter so that I am not looking at all 400 possiblities at once? I am so sorry if this is too long and I'm asking too many questions at once. I understand that you are trying to help as many people as possible. I have searched and searched for answers and tried to adapt the customers/orders format as much as possible to my situation but...I need help. -- Aria W. |
#2
|
|||
|
|||
Still Struggling...
Responses inline.
"Aria" wrote in message ... Hi, I'm still struggling to organize my tables and fields but I'm not as *totally confused* as my original post; at least I hope I'm not. I'm new to Access. This is my first database and I fluctuate between feeling hopeful I can do this one minute and despairing that I can't the next because I've thought of yet another complication that I don't know how to handle. I have scoured the Internet and this disscussion group searching for the answers that I need. I found some but I am still at a loss as to what to do about others. I was wondering if someone would be kind enough to review my table structure and respond to some questions at the end. I am truly grateful. The information is as follows: tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text I ws going to say that SchoolData and PersonalInfo should be broken into several fields each, but later you said something about the number of fields in the table, so it seems the way you have written it here is a sort of shorthand, and that you have separate fields as needed. tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text One lock could have several keys (one for each of several people). This suggests you need a Locks table, separate from the Keys table. Each lock could have several keys, so there is a one-to-many relationship between locks and keys. Since a filing cabinet could be moved from one room to another, the correlation between locks and rooms may need to be flexible. One approach to the Locks table would be to have fields for Campus, Wing, and Room. You could query the table for locks that go with a particular campus, building, wing, and room, or for all of the locks for a campus, or all of the locks for a building, etc. You may want to build some lookup tables (very different from lookup fields) to use for selecting campus, wing, and roomtype from combo boxes on your form. For instance, a RoomType table may be simply a listing of RoomTypes. Make a query based on this table (sorted by RoomType), and use the query as the Row Source for a RoomType combo box on your Keys form. tblKeysEmployees Key ID- composite key(FK to tblKeys)- text EmployeeID-composite key (FK to tblEmployees)- Autonumber EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign key field, so it cannot be assigned automatically. Rather, it gets its value from the parent table when a record is created. The two fields together can be a composite PK for this table, but are not by themselves composite keys. tblKeysRequests Request ID-Autonumber Key ID (FK to tblKeys)- text Emp. ID Rm. number- text--I think there is a problem here. This info is part of tblrooms. Rm. phone- text Date Requested- Date/time Date Recvd.- Date/time Date Issued- Date/time tblRooms Key ID (PK)-text Room number- text Remarks-text KeyID as the PK of tblRooms could be confusing. tblSubs Sub ID (PK)-Autonumber SubLN-text SubFN-text MI-text SubPhone-text Key ID (FK to tblKeys) Date Issued-Date/Time Date Returned-Date/Time This table's purpose is unclear. If you're still reading, I have the following questions: 1. How many fields are too many in a table? I understand that Access will accept up to 255. I read a post that suggested that 20-30 fields may indicate there may be a normalization problem. I'm wondering about tblEmployees where there are approx. 25 fields. No problem. More than 30 fields could suggest normalization problems, but it not an invariable rule. If the SchoolData fields are not all filled in for every record it could be that SchoolData could be in a separate table. Nothing else stands out much. 2.tblRooms has a field for Rm.#; the problem is that not all rooms have a #. Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks to tblRooms because I may have to describe a location rather than a room #. Will there be a problem with this method? Only if RoomNumber is a linking field. By the way, I assume you are using the number sign for description, not as a field name. Names should include only letters, number, and underscores. Spaces and other non-alphanumeric characters are best avoided. One of the desired reports is a reverse directory by room #/room phone #. The phone # is currently in tblEmployees. Should it be moved to tblRooms? If I leave it where it is, will I be able to update any queries and reports? I've read that multitable queries aren't updateable. We have had mass staff room changes right before the school year begins. It just seems it would be better if the phone # was listed with tblRooms but someone told me that's incorrect. If the phone number is associated with a room it should be part of a room record. Presumably an employee can be associated with several rooms, and vice versa. However, if an employee has an office where only he or she answers the phone, the phone number is associated with the employee. Often in such case the phone number follows the employee if they move to another office. There is no single answer to your question. Your database can be made to accept either a phone associated with a room or with an employee, or both. It does start to become more complex if you have both situations. 3. I forgot to include the following fields: Date assigned, Return Date and Permission to Retain (over the summer) for Emp. Keys. Should this be added to tblKeys or tblEmployees? I was told not to put *anything* into the junction table (tblKeysEmployees) besides the composite key already listed. You can have other fields in a junction table. For instance, the KeysEmployees junction table may include a DateIssued field, which is a property of neither the Employee nor the Key, but rather of this particular key together with this particular employee. If a key may be retained over the summer by one employee, but not another, then this too is a candidate for a field in the junction table. 4.Keys Requests--Somethimes are request will not have a name associated with it. For instance, we need to request a key for the vault. I have not accounted for this situation and don't know how to handle it. If I need to order a sub. key, I can use the name of the permanent staff member associated with that room. What do I do about the vault? Should I enter "vault" in the emp. table w/o any additional data? Vault chould not be in the employee table, but the situation is unclear. Is nobody responsible for the vault key? How will you know who has it? How do you know now? 5. Maybe I should cross this bridge when I get to it but I eventually will get to the point where I will make forms and subforms. There are approx. 400 seperate keys, not including multiple copies of the same key. If I have a combo box, is there a way to filter so that I am not looking at all 400 possiblities at once? Again, I'm not sure what you're asking. You will need forms and subforms as soon as you start doing data entry. It may be best to begin with just tblKeys, tblEmployees, and tblKeysEmployees, just as a test to see how to work with the junction table arrangement. I am so sorry if this is too long and I'm asking too many questions at once. I understand that you are trying to help as many people as possible. I have searched and searched for answers and tried to adapt the customers/orders format as much as possible to my situation but...I need help. -- Aria W. |
#3
|
|||
|
|||
Still Struggling...
Bruce,
First, I would really like to thank you for responding. I need to clarify some of the information that seems to be confusing. tblSubs is a table for temporary, roving district staff. They may be employed at any district site anywhere from a couple of hours to long-term(months). They need access to their assignment location and if they are certificated, temporary passwords for certain reports. My comments are as follows: tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text I ws going to say that SchoolData and PersonalInfo should be broken into several fields each, but later you said something about the number of fields in the table, so it seems the way you have written it here is a sort of shorthand, and that you have separate fields as needed. I think I'm fairly comfortable with this table. I just thought maybe it needed to be broken down (not that I'm looking for another table to put in). Yes, the fields are seperate. tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text One lock could have several keys (one for each of several people). This suggests you need a Locks table, separate from the Keys table. Each lock could have several keys, so there is a one-to-many relationship between locks and keys. Since a filing cabinet could be moved from one room to another, the correlation between locks and rooms may need to be flexible. One approach to the Locks table would be to have fields for Campus, Wing, and Room. You could query the table for locks that go with a particular campus, building, wing, and room, or for all of the locks for a campus, or all of the locks for a building, etc. You may want to build some lookup tables (very different from lookup fields) to use for selecting campus, wing, and roomtype from combo boxes on your form. For instance, a RoomType table may be simply a listing of RoomTypes. Make a query based on this table (sorted by RoomType), and use the query as the Row Source for a RoomType combo box on your Keys form. Now, this is where you lose me. I don't understand the purpose of a Locks table. Isn't that what the keys table is for? We are only interested in the locks as far as what key will staff to gain access to the room, gate, stadium, etc. We don't normally track file cabinet keys. We may if the contents are extremely confidential. tblKeysEmployees Key ID- composite key(FK to tblKeys)- text EmployeeID-composite key (FK to tblEmployees)- Autonumber EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign key field, so it cannot be assigned automatically. Rather, it gets its value from the parent table when a record is created. The two fields together can be a composite PK for this table, but are not by themselves composite keys. Sorry; I dropped the ball on this one. This *is* long interger. I forgot to make the change in designation from my original. tblRooms Key ID (PK)-text Room number- text Remarks-text KeyID as the PK of tblRooms could be confusing. Again, you lost me on this one. Why is the KeyID confusing? It relates to the room; knowing the key ID will allow you access. My thinking was that if I could do a parameter query (is that what I would want?) by room, I would know which key will unlock it. Then I could make a key request or whatever else needs to be done. tblSubs Sub ID (PK)-Autonumber SubLN-text SubFN-text MI-text SubPhone-text Key ID (FK to tblKeys) Date Issued-Date/Time Date Returned-Date/Time This table's purpose is unclear. I'm sorry. I should have explained in the beginning. Like I stated, this is for temporary, roving staff. We need Access only in relation to keys for this group. We have other applications for the rest of the information we need. 1. How many fields are too many in a table? I understand that Access will accept up to 255. I read a post that suggested that 20-30 fields may indicate there may be a normalization problem. I'm wondering about tblEmployees where there are approx. 25 fields. No problem. More than 30 fields could suggest normalization problems, but it not an invariable rule. If the SchoolData fields are not all filled in for every record it could be that SchoolData could be in a separate table. Nothing else stands out much. All the fields relating to school data wil be filled in, so I guess we're good here. 2.tblRooms has a field for Rm.#; the problem is that not all rooms have a #. Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks to tblRooms because I may have to describe a location rather than a room #. Will there be a problem with this method? Only if RoomNumber is a linking field. By the way, I assume you are using the number sign for description, not as a field name. Names should include only letters, number, and underscores. Spaces and other non-alphanumeric characters are best avoided. Oh my gosh! How many times have I read the same thing and because I *knew* my fields weren't like that, moved on. Well, I just wanted to double check and lo and behold, there it was; a # sign in my field name. Thanks for the heads up! One of the desired reports is a reverse directory by room #/room phone #. The phone # is currently in tblEmployees. Should it be moved to tblRooms? If I leave it where it is, will I be able to update any queries and reports? I've read that multitable queries aren't updateable. We have had mass staff room changes right before the school year begins. It just seems it would be better if the phone # was listed with tblRooms but someone told me that's incorrect. If the phone number is associated with a room it should be part of a room record. Presumably an employee can be associated with several rooms, and vice versa. However, if an employee has an office where only he or she answers the phone, the phone number is associated with the employee. Often in such case the phone number follows the employee if they move to another office. There is no single answer to your question. Your database can be made to accept either a phone associated with a room or with an employee, or both. It does start to become more complex if you have both situations. Well, for classroom staff, if they have to change rooms (happens every year, so I have no illusions here), they switch to the new rooms' phone #. For office staff, it could go either way. What do I need to do if anything? 3. I forgot to include the following fields: Date assigned, Return Date and Permission to Retain (over the summer) for Emp. Keys. Should this be added to tblKeys or tblEmployees? I was told not to put *anything* into the junction table (tblKeysEmployees) besides the composite key already listed. You can have other fields in a junction table. For instance, the KeysEmployees junction table may include a DateIssued field, which is a property of neither the Employee nor the Key, but rather of this particular key together with this particular employee. If a key may be retained over the summer by one employee, but not another, then this too is a candidate for a field in the junction table. Your statements are true enough; only the master keys are coded to a particular employee. Classroom keys are a little more generic in that anyone who works out of that classroom or office will have the same key. O.k., I'll put the new fields here. 4.Keys Requests--Sometimes a request will not have a name associated with it. For instance, we need to request a key for the vault. I have not accounted for this situation and don't know how to handle it. If I need to order a sub. key, I can use the name of the permanent staff member associated with that room. What do I do about the vault? Should I enter "vault" in the emp. table w/o any additional data? Vault chould not be in the employee table, but the situation is unclear. Is nobody responsible for the vault key? How will you know who has it? How do you know now? Every key available on both campuses should be in the vault. Theoretically, vault keys remain in the vault. I guess I'm responsible for those keys but anyone who has the combination can remove a key without my knowledge. I guess I could list myself and under Remarks indicate it's a vault request. 5. Maybe I should cross this bridge when I get to it but I eventually will get to the point where I will make forms and subforms. There are approx. 400 seperate keys, not including multiple copies of the same key. If I have a combo box, is there a way to filter so that I am not looking at all 400 possiblities at once? Again, I'm not sure what you're asking. You will need forms and subforms as soon as you start doing data entry. It may be best to begin with just tblKeys, tblEmployees, and tblKeysEmployees, just as a test to see how to work with the junction table arrangement. I'll hold off on this question and concentrate on getting the tables and fields correct. Bruce, again, I really appreciate all of your help. Thanks so much for talking me through it! -- Aria W. "BruceM" wrote: Responses inline. "Aria" wrote in message ... Hi, I'm still struggling to organize my tables and fields but I'm not as *totally confused* as my original post; at least I hope I'm not. I'm new to Access. This is my first database and I fluctuate between feeling hopeful I can do this one minute and despairing that I can't the next because I've thought of yet another complication that I don't know how to handle. I have scoured the Internet and this disscussion group searching for the answers that I need. I found some but I am still at a loss as to what to do about others. I was wondering if someone would be kind enough to review my table structure and respond to some questions at the end. I am truly grateful. The information is as follows: tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text I ws going to say that SchoolData and PersonalInfo should be broken into several fields each, but later you said something about the number of fields in the table, so it seems the way you have written it here is a sort of shorthand, and that you have separate fields as needed. tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text One lock could have several keys (one for each of several people). This suggests you need a Locks table, separate from the Keys table. Each lock could have several keys, so there is a one-to-many relationship between locks and keys. Since a filing cabinet could be moved from one room to another, the correlation between locks and rooms may need to be flexible. One approach to the Locks table would be to have fields for Campus, Wing, and Room. You could query the table for locks that go with a particular campus, building, wing, and room, or for all of the locks for a campus, or all of the locks for a building, etc. You may want to build some lookup tables (very different from lookup fields) to use for selecting campus, wing, and roomtype from combo boxes on your form. For instance, a RoomType table may be simply a listing of RoomTypes. Make a query based on this table (sorted by RoomType), and use the query as the Row Source for a RoomType combo box on your Keys form. tblKeysEmployees Key ID- composite key(FK to tblKeys)- text EmployeeID-composite key (FK to tblEmployees)- Autonumber EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign key field, so it cannot be assigned automatically. Rather, it gets its value from the parent table when a record is created. The two fields together can be a composite PK for this table, but are not by themselves composite keys. tblKeysRequests Request ID-Autonumber Key ID (FK to tblKeys)- text Emp. ID Rm. number- text--I think there is a problem here. This info is part of tblrooms. Rm. phone- text Date Requested- Date/time Date Recvd.- Date/time Date Issued- Date/time tblRooms Key ID (PK)-text Room number- text Remarks-text KeyID as the PK of tblRooms could be confusing. tblSubs Sub ID (PK)-Autonumber SubLN-text SubFN-text MI-text SubPhone-text Key ID (FK to tblKeys) Date Issued-Date/Time Date Returned-Date/Time This table's purpose is unclear. If you're still reading, I have the following questions: 1. How many fields are too many in a table? I understand that Access will accept up to 255. I read a post that suggested that 20-30 fields may indicate there may be a normalization problem. I'm wondering about tblEmployees where there are approx. 25 fields. No problem. More than 30 fields could suggest normalization problems, but it not an invariable rule. If the SchoolData fields are not all filled in for every record it could be that SchoolData could be in a separate table. Nothing else stands out much. 2.tblRooms has a field for Rm.#; the problem is that not all rooms have a #. Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks to tblRooms because I may have to describe a location rather than a room #. Will there be a problem with this method? Only if RoomNumber is a linking field. By the way, I assume you are using the number sign for description, not as a field name. Names should include only letters, number, and underscores. Spaces and other non-alphanumeric characters are best avoided. One of the desired reports is a reverse directory by room #/room phone #. The phone # is currently in tblEmployees. Should it be moved to tblRooms? If I leave it where it is, will I be able to update any queries and reports? I've read that multitable queries aren't updateable. We have had mass staff room changes right before the school year begins. It just seems it would be better if the phone # was listed with tblRooms but someone told me that's incorrect. If the phone number is associated with a room it should be part of a room record. Presumably an employee can be associated with several rooms, and vice versa. However, if an employee has an office where only he or she answers the phone, the phone number is associated with the employee. Often in such case the phone number follows the employee if they move to another office. There is no single answer to your question. Your database can be made to accept either a phone associated with a room or with an employee, or both. It does start to become more complex if you have both situations. 3. I forgot to include the following fields: Date assigned, Return Date and Permission to Retain (over the summer) for Emp. Keys. Should this be added to tblKeys or tblEmployees? I was told not to put *anything* into the junction table (tblKeysEmployees) besides the composite key already listed. You can have other fields in a junction table. For instance, the KeysEmployees junction table may include a DateIssued field, which is a property of neither the Employee nor the Key, but rather of this particular key together with this particular employee. If a key may be retained over the summer by one employee, but not another, then this too is a candidate for a field in the junction table. 4.Keys Requests--Somethimes are request will not have a name associated with it. For instance, we need to request a key for the vault. I have not accounted for this situation and don't know how to handle it. If I need to order a sub. key, I can use the name of the permanent staff member associated with that room. What do I do about the vault? Should I enter "vault" in the emp. table w/o any additional data? Vault chould not be in the employee table, but the situation is unclear. Is nobody responsible for the vault key? How will you know who has it? How do you know now? 5. Maybe I should cross this bridge when I get to it but I eventually will get to the point where I will make forms and subforms. There are approx. 400 seperate keys, not including multiple copies of the same key. If I have a combo box, is there a way to filter so that I am not looking at all 400 possiblities at once? Again, I'm not sure what you're asking. You will need forms and subforms as soon as you start doing data entry. It may be best to begin with just tblKeys, tblEmployees, and tblKeysEmployees, just as a test to see how to work with the junction table arrangement. I am so sorry if this is too long and I'm asking too many questions at once. I understand that you are trying to help as many people as possible. I have searched and searched for answers and tried to adapt the customers/orders format as much as possible to my situation but...I need help. -- Aria W. |
#4
|
|||
|
|||
Still Struggling...
Hate to throw in the monkey wrench, but I'm going to have to disagree
with some of what's been posted (comments inline). tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text Sorry, but the school data belongs in a separate table/tables. With the above structure you will repeatedly be entering the same descriptive data over and over. Additionally, what if an instructor teaches more than one Subject, works in more than one Dept., etc? You will have to enter additional records for the same employee. tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text Again, this is wrong. Campus, Wing and RoomType are not attributes of the Key. This table should have RoomID as a foreign key to tblRooms and probably not much else, unless you store some other descriptive information about the key itself, like "color" or something. Think of it this way - you said you don't really track filing cabinet keys so we'll just take rooms into consideration here. A key opens a room. Period. The Room Type and the Wing in which that room is located are attributes of the Room, not the Key. Likewise, the Campus that Wing is located in is an attribute of the Wing (not the Room or the Key). You should have separate tables for Campus' and Wings tblRooms Key ID (PK)-text Room number- text Remarks-text This table should have RoomID as a PK (KeyID does not belong here). A room can have many keys, but a key can only open one room, so RoomID goes in tblKeys as a foreign key, not the other way around. tblSubs Sub ID (PK)-Autonumber SubLN-text SubFN-text MI-text SubPhone-text Key ID (FK to tblKeys) Date Issued-Date/Time Date Returned-Date/Time IMO this table is unecessary. Whether an employee is "full time" or "temporary" is just an attribute that would go in tblEmployees. The fact that a person may come and go as a member of your staff is irrelevant to the fact that that person was in posession of a certain key at a certain time. The date that a key was issued/returned belongs in tblKeysEmployees. You might be thinking "great, now I have to add more tables" but keep in mind that in an application like this you will typically have several tables that will basically just be "lookup" tables. They aren't really a big deal to set up. Just from the little I know about your app, I would say you would probably have the following tables that would be "lookup" tables; tblClassifications tblDepartments tblSubjects tblCampuses tblWings tblRoomTypes You may also need some additional junction tables, depending on if - as I stated earlier - an instructor can teach more than one subject, work in more than one department, etc. For you first app, you didn't exactly pick a simple one, but everyone loves a challenge, right? g Good luck and welcome to the world of Access. -- _________ Sean Bailey "Aria" wrote: Bruce, First, I would really like to thank you for responding. I need to clarify some of the information that seems to be confusing. tblSubs is a table for temporary, roving district staff. They may be employed at any district site anywhere from a couple of hours to long-term(months). They need access to their assignment location and if they are certificated, temporary passwords for certain reports. My comments are as follows: tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text I ws going to say that SchoolData and PersonalInfo should be broken into several fields each, but later you said something about the number of fields in the table, so it seems the way you have written it here is a sort of shorthand, and that you have separate fields as needed. I think I'm fairly comfortable with this table. I just thought maybe it needed to be broken down (not that I'm looking for another table to put in). Yes, the fields are seperate. tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text One lock could have several keys (one for each of several people). This suggests you need a Locks table, separate from the Keys table. Each lock could have several keys, so there is a one-to-many relationship between locks and keys. Since a filing cabinet could be moved from one room to another, the correlation between locks and rooms may need to be flexible. One approach to the Locks table would be to have fields for Campus, Wing, and Room. You could query the table for locks that go with a particular campus, building, wing, and room, or for all of the locks for a campus, or all of the locks for a building, etc. You may want to build some lookup tables (very different from lookup fields) to use for selecting campus, wing, and roomtype from combo boxes on your form. For instance, a RoomType table may be simply a listing of RoomTypes. Make a query based on this table (sorted by RoomType), and use the query as the Row Source for a RoomType combo box on your Keys form. Now, this is where you lose me. I don't understand the purpose of a Locks table. Isn't that what the keys table is for? We are only interested in the locks as far as what key will staff to gain access to the room, gate, stadium, etc. We don't normally track file cabinet keys. We may if the contents are extremely confidential. tblKeysEmployees Key ID- composite key(FK to tblKeys)- text EmployeeID-composite key (FK to tblEmployees)- Autonumber EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign key field, so it cannot be assigned automatically. Rather, it gets its value from the parent table when a record is created. The two fields together can be a composite PK for this table, but are not by themselves composite keys. Sorry; I dropped the ball on this one. This *is* long interger. I forgot to make the change in designation from my original. tblRooms Key ID (PK)-text Room number- text Remarks-text KeyID as the PK of tblRooms could be confusing. Again, you lost me on this one. Why is the KeyID confusing? It relates to the room; knowing the key ID will allow you access. My thinking was that if I could do a parameter query (is that what I would want?) by room, I would know which key will unlock it. Then I could make a key request or whatever else needs to be done. tblSubs Sub ID (PK)-Autonumber SubLN-text SubFN-text MI-text SubPhone-text Key ID (FK to tblKeys) Date Issued-Date/Time Date Returned-Date/Time This table's purpose is unclear. I'm sorry. I should have explained in the beginning. Like I stated, this is for temporary, roving staff. We need Access only in relation to keys for this group. We have other applications for the rest of the information we need. 1. How many fields are too many in a table? I understand that Access will accept up to 255. I read a post that suggested that 20-30 fields may indicate there may be a normalization problem. I'm wondering about tblEmployees where there are approx. 25 fields. No problem. More than 30 fields could suggest normalization problems, but it not an invariable rule. If the SchoolData fields are not all filled in for every record it could be that SchoolData could be in a separate table. Nothing else stands out much. All the fields relating to school data wil be filled in, so I guess we're good here. 2.tblRooms has a field for Rm.#; the problem is that not all rooms have a #. Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks to tblRooms because I may have to describe a location rather than a room #. Will there be a problem with this method? Only if RoomNumber is a linking field. By the way, I assume you are using the number sign for description, not as a field name. Names should include only letters, number, and underscores. Spaces and other non-alphanumeric characters are best avoided. Oh my gosh! How many times have I read the same thing and because I *knew* my fields weren't like that, moved on. Well, I just wanted to double check and lo and behold, there it was; a # sign in my field name. Thanks for the heads up! One of the desired reports is a reverse directory by room #/room phone #. The phone # is currently in tblEmployees. Should it be moved to tblRooms? If I leave it where it is, will I be able to update any queries and reports? I've read that multitable queries aren't updateable. We have had mass staff room changes right before the school year begins. It just seems it would be better if the phone # was listed with tblRooms but someone told me that's incorrect. If the phone number is associated with a room it should be part of a room record. Presumably an employee can be associated with several rooms, and vice versa. However, if an employee has an office where only he or she answers the phone, the phone number is associated with the employee. Often in such case the phone number follows the employee if they move to another office. There is no single answer to your question. Your database can be made to accept either a phone associated with a room or with an employee, or both. It does start to become more complex if you have both situations. Well, for classroom staff, if they have to change rooms (happens every year, so I have no illusions here), they switch to the new rooms' phone #. For office staff, it could go either way. What do I need to do if anything? 3. I forgot to include the following fields: Date assigned, Return Date and Permission to Retain (over the summer) for Emp. Keys. Should this be added to tblKeys or tblEmployees? I was told not to put *anything* into the junction table (tblKeysEmployees) besides the composite key already listed. You can have other fields in a junction table. For instance, the KeysEmployees junction table may include a DateIssued field, which is a property of neither the Employee nor the Key, but rather of this particular key together with this particular employee. If a key may be retained over the summer by one employee, but not another, then this too is a candidate for a field in the junction table. Your statements are true enough; only the master keys are coded to a particular employee. Classroom keys are a little more generic in that anyone who works out of that classroom or office will have the same key. O.k., I'll put the new fields here. 4.Keys Requests--Sometimes a request will not have a name associated with it. For instance, we need to request a key for the vault. I have not accounted for this situation and don't know how to handle it. If I need to order a sub. key, I can use the name of the permanent staff member associated with that room. What do I do about the vault? Should I enter "vault" in the emp. table w/o any additional data? Vault chould not be in the employee table, but the situation is unclear. Is nobody responsible for the vault key? How will you know who has it? How do you know now? Every key available on both campuses should be in the vault. Theoretically, vault keys remain in the vault. I guess I'm responsible for those keys but anyone who has the combination can remove a key without my knowledge. I guess I could list myself and under Remarks indicate it's a vault request. 5. Maybe I should cross this bridge when I get to it but I eventually will get to the point where I will make forms and subforms. There are approx. 400 seperate keys, not including multiple copies of the same key. If I have a combo box, is there a way to filter so that I am not looking at all 400 possiblities at once? Again, I'm not sure what you're asking. You will need forms and subforms as soon as you start doing data entry. It may be best to begin with just tblKeys, tblEmployees, and tblKeysEmployees, just as a test to see how to work with the junction table arrangement. I'll hold off on this question and concentrate on getting the tables and fields correct. Bruce, again, I really appreciate all of your help. Thanks so much for talking me through it! -- Aria W. "BruceM" wrote: Responses inline. "Aria" wrote in message ... Hi, I'm still struggling to organize my tables and fields but I'm not as *totally confused* as my original post; at least I hope I'm not. I'm new to Access. This is my first database and I fluctuate between feeling hopeful I can do this one minute and despairing that I can't the next because I've thought of yet another complication that I don't know how to handle. I have scoured the Internet and this disscussion group searching for the answers that I need. I found some but I am still at a loss as to what to do about others. I was wondering if someone would be kind enough to review my table structure and respond to some questions at the end. I am truly grateful. The information is as follows: tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text I ws going to say that SchoolData and PersonalInfo should be broken into several fields each, but later you said something about the number of fields in the table, so it seems the way you have written it here is a sort of shorthand, and that you have separate fields as needed. tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text One lock could have several keys (one for each of several people). This suggests you need a Locks table, separate from the Keys table. Each lock could have several keys, so there is a one-to-many relationship between locks and keys. Since a filing cabinet could be moved from one room to another, the correlation between locks and rooms may need to be flexible. One approach to the Locks table would be to have fields for Campus, Wing, and Room. You could query the table for locks that go with a particular campus, building, wing, and room, or for all of the locks for a campus, or all of the locks for a building, etc. You may want to build some lookup tables (very different from lookup fields) to use for selecting campus, wing, and roomtype from combo boxes on your form. For instance, a RoomType table may be simply a listing of RoomTypes. Make a query based on this table (sorted by RoomType), and use the query as the Row Source for a RoomType combo box on your Keys form. tblKeysEmployees Key ID- composite key(FK to tblKeys)- text EmployeeID-composite key (FK to tblEmployees)- Autonumber EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign key field, so it cannot be assigned automatically. Rather, it gets its value from the parent table when a record is created. The two fields together can be a composite PK for this table, but are not by themselves composite keys. tblKeysRequests Request ID-Autonumber Key ID (FK to tblKeys)- text Emp. ID Rm. number- text--I think there is a problem here. This info is part of tblrooms. |
#5
|
|||
|
|||
Still Struggling...
"Beetle" wrote in message
... Hate to throw in the monkey wrench, but I'm going to have to disagree with some of what's been posted (comments inline). tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text Sorry, but the school data belongs in a separate table/tables. With the above structure you will repeatedly be entering the same descriptive data over and over. Additionally, what if an instructor teaches more than one Subject, works in more than one Dept., etc? You will have to enter additional records for the same employee. You make some valid points about subjects and departments, and about titles too if somebody holds more than one. I'm not sure exactly what is meant by classification, so I couldn't say if there could be more than one. It depends in part on how they do things. However, the posting was about keys, so correctly or not I chose not to get into a discussion of the Employee table. tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text Again, this is wrong. Campus, Wing and RoomType are not attributes of the Key. This table should have RoomID as a foreign key to tblRooms and probably not much else, unless you store some other descriptive information about the key itself, like "color" or something. Think of it this way - you said you don't really track filing cabinet keys so we'll just take rooms into consideration here. A key opens a room. Period. The Room Type and the Wing in which that room is located are attributes of the Room, not the Key. Likewise, the Campus that Wing is located in is an attribute of the Wing (not the Room or the Key). You should have separate tables for Campus' and Wings You may be correct about the RoomID as the FK. However, it isn't necessarily as simple as a key opening a room (period). The building could have a key, for instance, or there could be a padlock or other lock that is not a room (or supply closet) lock. The OP said they don't "really" track file cabinet keys and such, but if they ever have a key to something other than a room provisions will need to be made for that. This is why I suggeested a Locks table. A lock could have several keys. A Locks table would need to specify the lock's location in a way that does not necessarily reference a room (in what room is a building's front door located?). Also, a room having several different locks (in an auditorium, for instance, or a room with an outside door and a hallway door) is not out of the question. tblRooms Key ID (PK)-text Room number- text Remarks-text This table should have RoomID as a PK (KeyID does not belong here). A room can have many keys, but a key can only open one room, so RoomID goes in tblKeys as a foreign key, not the other way around. tblSubs Sub ID (PK)-Autonumber SubLN-text SubFN-text MI-text SubPhone-text Key ID (FK to tblKeys) Date Issued-Date/Time Date Returned-Date/Time IMO this table is unecessary. Whether an employee is "full time" or "temporary" is just an attribute that would go in tblEmployees. The fact that a person may come and go as a member of your staff is irrelevant to the fact that that person was in posession of a certain key at a certain time. The date that a key was issued/returned belongs in tblKeysEmployees. You might be thinking "great, now I have to add more tables" but keep in mind that in an application like this you will typically have several tables that will basically just be "lookup" tables. They aren't really a big deal to set up. Just from the little I know about your app, I would say you would probably have the following tables that would be "lookup" tables; tblClassifications tblDepartments tblSubjects tblCampuses tblWings tblRoomTypes You may also need some additional junction tables, depending on if - as I stated earlier - an instructor can teach more than one subject, work in more than one department, etc. For you first app, you didn't exactly pick a simple one, but everyone loves a challenge, right? g Good luck and welcome to the world of Access. -- _________ Sean Bailey "Aria" wrote: Bruce, First, I would really like to thank you for responding. I need to clarify some of the information that seems to be confusing. tblSubs is a table for temporary, roving district staff. They may be employed at any district site anywhere from a couple of hours to long-term(months). They need access to their assignment location and if they are certificated, temporary passwords for certain reports. My comments are as follows: tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text I ws going to say that SchoolData and PersonalInfo should be broken into several fields each, but later you said something about the number of fields in the table, so it seems the way you have written it here is a sort of shorthand, and that you have separate fields as needed. I think I'm fairly comfortable with this table. I just thought maybe it needed to be broken down (not that I'm looking for another table to put in). Yes, the fields are seperate. tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text One lock could have several keys (one for each of several people). This suggests you need a Locks table, separate from the Keys table. Each lock could have several keys, so there is a one-to-many relationship between locks and keys. Since a filing cabinet could be moved from one room to another, the correlation between locks and rooms may need to be flexible. One approach to the Locks table would be to have fields for Campus, Wing, and Room. You could query the table for locks that go with a particular campus, building, wing, and room, or for all of the locks for a campus, or all of the locks for a building, etc. You may want to build some lookup tables (very different from lookup fields) to use for selecting campus, wing, and roomtype from combo boxes on your form. For instance, a RoomType table may be simply a listing of RoomTypes. Make a query based on this table (sorted by RoomType), and use the query as the Row Source for a RoomType combo box on your Keys form. Now, this is where you lose me. I don't understand the purpose of a Locks table. Isn't that what the keys table is for? We are only interested in the locks as far as what key will staff to gain access to the room, gate, stadium, etc. We don't normally track file cabinet keys. We may if the contents are extremely confidential. tblKeysEmployees Key ID- composite key(FK to tblKeys)- text EmployeeID-composite key (FK to tblEmployees)- Autonumber EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign key field, so it cannot be assigned automatically. Rather, it gets its value from the parent table when a record is created. The two fields together can be a composite PK for this table, but are not by themselves composite keys. Sorry; I dropped the ball on this one. This *is* long interger. I forgot to make the change in designation from my original. tblRooms Key ID (PK)-text Room number- text Remarks-text KeyID as the PK of tblRooms could be confusing. Again, you lost me on this one. Why is the KeyID confusing? It relates to the room; knowing the key ID will allow you access. My thinking was that if I could do a parameter query (is that what I would want?) by room, I would know which key will unlock it. Then I could make a key request or whatever else needs to be done. tblSubs Sub ID (PK)-Autonumber SubLN-text SubFN-text MI-text SubPhone-text Key ID (FK to tblKeys) Date Issued-Date/Time Date Returned-Date/Time This table's purpose is unclear. I'm sorry. I should have explained in the beginning. Like I stated, this is for temporary, roving staff. We need Access only in relation to keys for this group. We have other applications for the rest of the information we need. 1. How many fields are too many in a table? I understand that Access will accept up to 255. I read a post that suggested that 20-30 fields may indicate there may be a normalization problem. I'm wondering about tblEmployees where there are approx. 25 fields. No problem. More than 30 fields could suggest normalization problems, but it not an invariable rule. If the SchoolData fields are not all filled in for every record it could be that SchoolData could be in a separate table. Nothing else stands out much. All the fields relating to school data wil be filled in, so I guess we're good here. 2.tblRooms has a field for Rm.#; the problem is that not all rooms have a #. Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks to tblRooms because I may have to describe a location rather than a room #. Will there be a problem with this method? Only if RoomNumber is a linking field. By the way, I assume you are using the number sign for description, not as a field name. Names should include only letters, number, and underscores. Spaces and other non-alphanumeric characters are best avoided. Oh my gosh! How many times have I read the same thing and because I *knew* my fields weren't like that, moved on. Well, I just wanted to double check and lo and behold, there it was; a # sign in my field name. Thanks for the heads up! One of the desired reports is a reverse directory by room #/room phone #. The phone # is currently in tblEmployees. Should it be moved to tblRooms? If I leave it where it is, will I be able to update any queries and reports? I've read that multitable queries aren't updateable. We have had mass staff room changes right before the school year begins. It just seems it would be better if the phone # was listed with tblRooms but someone told me that's incorrect. If the phone number is associated with a room it should be part of a room record. Presumably an employee can be associated with several rooms, and vice versa. However, if an employee has an office where only he or she answers the phone, the phone number is associated with the employee. Often in such case the phone number follows the employee if they move to another office. There is no single answer to your question. Your database can be made to accept either a phone associated with a room or with an employee, or both. It does start to become more complex if you have both situations. Well, for classroom staff, if they have to change rooms (happens every year, so I have no illusions here), they switch to the new rooms' phone #. For office staff, it could go either way. What do I need to do if anything? 3. I forgot to include the following fields: Date assigned, Return Date and Permission to Retain (over the summer) for Emp. Keys. Should this be added to tblKeys or tblEmployees? I was told not to put *anything* into the junction table (tblKeysEmployees) besides the composite key already listed. You can have other fields in a junction table. For instance, the KeysEmployees junction table may include a DateIssued field, which is a property of neither the Employee nor the Key, but rather of this particular key together with this particular employee. If a key may be retained over the summer by one employee, but not another, then this too is a candidate for a field in the junction table. Your statements are true enough; only the master keys are coded to a particular employee. Classroom keys are a little more generic in that anyone who works out of that classroom or office will have the same key. O.k., I'll put the new fields here. 4.Keys Requests--Sometimes a request will not have a name associated with it. For instance, we need to request a key for the vault. I have not accounted for this situation and don't know how to handle it. If I need to order a sub. key, I can use the name of the permanent staff member associated with that room. What do I do about the vault? Should I enter "vault" in the emp. table w/o any additional data? Vault chould not be in the employee table, but the situation is unclear. Is nobody responsible for the vault key? How will you know who has it? How do you know now? Every key available on both campuses should be in the vault. Theoretically, vault keys remain in the vault. I guess I'm responsible for those keys but anyone who has the combination can remove a key without my knowledge. I guess I could list myself and under Remarks indicate it's a vault request. 5. Maybe I should cross this bridge when I get to it but I eventually will get to the point where I will make forms and subforms. There are approx. 400 seperate keys, not including multiple copies of the same key. If I have a combo box, is there a way to filter so that I am not looking at all 400 possiblities at once? Again, I'm not sure what you're asking. You will need forms and subforms as soon as you start doing data entry. It may be best to begin with just tblKeys, tblEmployees, and tblKeysEmployees, just as a test to see how to work with the junction table arrangement. I'll hold off on this question and concentrate on getting the tables and fields correct. Bruce, again, I really appreciate all of your help. Thanks so much for talking me through it! -- Aria W. "BruceM" wrote: Responses inline. "Aria" wrote in message ... Hi, I'm still struggling to organize my tables and fields but I'm not as *totally confused* as my original post; at least I hope I'm not. I'm new to Access. This is my first database and I fluctuate between feeling hopeful I can do this one minute and despairing that I can't the next because I've thought of yet another complication that I don't know how to handle. I have scoured the Internet and this disscussion group searching for the answers that I need. I found some but I am still at a loss as to what to do about others. I was wondering if someone would be kind enough to review my table structure and respond to some questions at the end. I am truly grateful. The information is as follows: tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text I ws going to say that SchoolData and PersonalInfo should be broken into several fields each, but later you said something about the number of fields in the table, so it seems the way you have written it here is a sort of shorthand, and that you have separate fields as needed. tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text One lock could have several keys (one for each of several people). This suggests you need a Locks table, separate from the Keys table. Each lock could have several keys, so there is a one-to-many relationship between locks and keys. Since a filing cabinet could be moved from one room to another, the correlation between locks and rooms may need to be flexible. One approach to the Locks table would be to have fields for Campus, Wing, and Room. You could query the table for locks that go with a particular campus, building, wing, and room, or for all of the locks for a campus, or all of the locks for a building, etc. You may want to build some lookup tables (very different from lookup fields) to use for selecting campus, wing, and roomtype from combo boxes on your form. For instance, a RoomType table may be simply a listing of RoomTypes. Make a query based on this table (sorted by RoomType), and use the query as the Row Source for a RoomType combo box on your Keys form. tblKeysEmployees Key ID- composite key(FK to tblKeys)- text EmployeeID-composite key (FK to tblEmployees)- Autonumber EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign key field, so it cannot be assigned automatically. Rather, it gets its value from the parent table when a record is created. The two fields together can be a composite PK for this table, but are not by themselves composite keys. tblKeysRequests Request ID-Autonumber Key ID (FK to tblKeys)- text Emp. ID Rm. number- text--I think there is a problem here. This info is part of tblrooms. |
#6
|
|||
|
|||
Still Struggling...
"Aria" wrote in message ... Bruce, First, I would really like to thank you for responding. I need to clarify some of the information that seems to be confusing. tblSubs is a table for temporary, roving district staff. They may be employed at any district site anywhere from a couple of hours to long-term(months). They need access to their assignment location and if they are certificated, temporary passwords for certain reports. My comments are as follows: tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text I ws going to say that SchoolData and PersonalInfo should be broken into several fields each, but later you said something about the number of fields in the table, so it seems the way you have written it here is a sort of shorthand, and that you have separate fields as needed. I think I'm fairly comfortable with this table. I just thought maybe it needed to be broken down (not that I'm looking for another table to put in). Yes, the fields are seperate. tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text One lock could have several keys (one for each of several people). This suggests you need a Locks table, separate from the Keys table. Each lock could have several keys, so there is a one-to-many relationship between locks and keys. Since a filing cabinet could be moved from one room to another, the correlation between locks and rooms may need to be flexible. One approach to the Locks table would be to have fields for Campus, Wing, and Room. You could query the table for locks that go with a particular campus, building, wing, and room, or for all of the locks for a campus, or all of the locks for a building, etc. You may want to build some lookup tables (very different from lookup fields) to use for selecting campus, wing, and roomtype from combo boxes on your form. For instance, a RoomType table may be simply a listing of RoomTypes. Make a query based on this table (sorted by RoomType), and use the query as the Row Source for a RoomType combo box on your Keys form. Now, this is where you lose me. I don't understand the purpose of a Locks table. Isn't that what the keys table is for? We are only interested in the locks as far as what key will staff to gain access to the room, gate, stadium, etc. We don't normally track file cabinet keys. We may if the contents are extremely confidential. A lock could have several keys. If a supply closet has three keys issued and a fourth person needs a key, you need to know that another key has to be made. The keys is an attribute of the lock, not the other way around. At least that's how I see it. If a lock is changed you need a way to find the existing keys so they can be swapped with the new ones. tblKeysEmployees Key ID- composite key(FK to tblKeys)- text EmployeeID-composite key (FK to tblEmployees)- Autonumber EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign key field, so it cannot be assigned automatically. Rather, it gets its value from the parent table when a record is created. The two fields together can be a composite PK for this table, but are not by themselves composite keys. Sorry; I dropped the ball on this one. This *is* long interger. I forgot to make the change in designation from my original. tblRooms Key ID (PK)-text Room number- text Remarks-text KeyID as the PK of tblRooms could be confusing. Again, you lost me on this one. Why is the KeyID confusing? It relates to the room; knowing the key ID will allow you access. My thinking was that if I could do a parameter query (is that what I would want?) by room, I would know which key will unlock it. Then I could make a key request or whatever else needs to be done. As Beetle pointed out, RoomID is the PK of tblRooms (although again, I would identify Locks, which are not necessarily for rooms). A room (or lock) could have several keys, so KeyID is the FK to tblRooms (or tblLocks). tblSubs Sub ID (PK)-Autonumber SubLN-text SubFN-text MI-text SubPhone-text Key ID (FK to tblKeys) Date Issued-Date/Time Date Returned-Date/Time This table's purpose is unclear. I'm sorry. I should have explained in the beginning. Like I stated, this is for temporary, roving staff. We need Access only in relation to keys for this group. We have other applications for the rest of the information we need. Again, as Beetle suggested there is no need for this group to be separate from the people listed in tblEmployees (or tblPersonnel, if you prefer to look at it that way). 1. How many fields are too many in a table? I understand that Access will accept up to 255. I read a post that suggested that 20-30 fields may indicate there may be a normalization problem. I'm wondering about tblEmployees where there are approx. 25 fields. No problem. More than 30 fields could suggest normalization problems, but it not an invariable rule. If the SchoolData fields are not all filled in for every record it could be that SchoolData could be in a separate table. Nothing else stands out much. All the fields relating to school data wil be filled in, so I guess we're good here. Beetle made a good point about this if there somebody could have more than one subject, etc. I wish he had included more of the remarks when he replied. He posted that he was disagreeing with me (I think) about some things, but he did not include those things, so this thread is a bit fragmented. I have some other remarks in response to his reply. 2.tblRooms has a field for Rm.#; the problem is that not all rooms have a #. Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks to tblRooms because I may have to describe a location rather than a room #. Will there be a problem with this method? Only if RoomNumber is a linking field. By the way, I assume you are using the number sign for description, not as a field name. Names should include only letters, number, and underscores. Spaces and other non-alphanumeric characters are best avoided. Oh my gosh! How many times have I read the same thing and because I *knew* my fields weren't like that, moved on. Well, I just wanted to double check and lo and behold, there it was; a # sign in my field name. Thanks for the heads up! One of the desired reports is a reverse directory by room #/room phone #. The phone # is currently in tblEmployees. Should it be moved to tblRooms? If I leave it where it is, will I be able to update any queries and reports? I've read that multitable queries aren't updateable. We have had mass staff room changes right before the school year begins. It just seems it would be better if the phone # was listed with tblRooms but someone told me that's incorrect. If the phone number is associated with a room it should be part of a room record. Presumably an employee can be associated with several rooms, and vice versa. However, if an employee has an office where only he or she answers the phone, the phone number is associated with the employee. Often in such case the phone number follows the employee if they move to another office. There is no single answer to your question. Your database can be made to accept either a phone associated with a room or with an employee, or both. It does start to become more complex if you have both situations. Well, for classroom staff, if they have to change rooms (happens every year, so I have no illusions here), they switch to the new rooms' phone #. For office staff, it could go either way. What do I need to do if anything? If a staff member has a fixed phone number, that should be part of their Employee record. If a room has a phone number, that is an attribute of the room, so you would start to get into having the phone number be associated with a person's schedule, which is an other project. Essentially you would need to have the person's schedule. If they are in room 101 from 10:00 - noon, from those hours you would need to contact them there. I'm not sure there is a simple answer to this question. 3. I forgot to include the following fields: Date assigned, Return Date and Permission to Retain (over the summer) for Emp. Keys. Should this be added to tblKeys or tblEmployees? I was told not to put *anything* into the junction table (tblKeysEmployees) besides the composite key already listed. You can have other fields in a junction table. For instance, the KeysEmployees junction table may include a DateIssued field, which is a property of neither the Employee nor the Key, but rather of this particular key together with this particular employee. If a key may be retained over the summer by one employee, but not another, then this too is a candidate for a field in the junction table. Your statements are true enough; only the master keys are coded to a particular employee. Classroom keys are a little more generic in that anyone who works out of that classroom or office will have the same key. O.k., I'll put the new fields here. 4.Keys Requests--Sometimes a request will not have a name associated with it. For instance, we need to request a key for the vault. I have not accounted for this situation and don't know how to handle it. If I need to order a sub. key, I can use the name of the permanent staff member associated with that room. What do I do about the vault? Should I enter "vault" in the emp. table w/o any additional data? Vault chould not be in the employee table, but the situation is unclear. Is nobody responsible for the vault key? How will you know who has it? How do you know now? Every key available on both campuses should be in the vault. Theoretically, vault keys remain in the vault. I guess I'm responsible for those keys but anyone who has the combination can remove a key without my knowledge. I guess I could list myself and under Remarks indicate it's a vault request. It sounds as if the vault contains the originals from which copies are made. If so, and if the vault has a combination lock, I'm not sure the vault fits into your schema. 5. Maybe I should cross this bridge when I get to it but I eventually will get to the point where I will make forms and subforms. There are approx. 400 seperate keys, not including multiple copies of the same key. If I have a combo box, is there a way to filter so that I am not looking at all 400 possiblities at once? Again, I'm not sure what you're asking. You will need forms and subforms as soon as you start doing data entry. It may be best to begin with just tblKeys, tblEmployees, and tblKeysEmployees, just as a test to see how to work with the junction table arrangement. I'll hold off on this question and concentrate on getting the tables and fields correct. Bruce, again, I really appreciate all of your help. Thanks so much for talking me through it! -- Aria W. "BruceM" wrote: Responses inline. "Aria" wrote in message ... Hi, I'm still struggling to organize my tables and fields but I'm not as *totally confused* as my original post; at least I hope I'm not. I'm new to Access. This is my first database and I fluctuate between feeling hopeful I can do this one minute and despairing that I can't the next because I've thought of yet another complication that I don't know how to handle. I have scoured the Internet and this disscussion group searching for the answers that I need. I found some but I am still at a loss as to what to do about others. I was wondering if someone would be kind enough to review my table structure and respond to some questions at the end. I am truly grateful. The information is as follows: tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text I ws going to say that SchoolData and PersonalInfo should be broken into several fields each, but later you said something about the number of fields in the table, so it seems the way you have written it here is a sort of shorthand, and that you have separate fields as needed. tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text One lock could have several keys (one for each of several people). This suggests you need a Locks table, separate from the Keys table. Each lock could have several keys, so there is a one-to-many relationship between locks and keys. Since a filing cabinet could be moved from one room to another, the correlation between locks and rooms may need to be flexible. One approach to the Locks table would be to have fields for Campus, Wing, and Room. You could query the table for locks that go with a particular campus, building, wing, and room, or for all of the locks for a campus, or all of the locks for a building, etc. You may want to build some lookup tables (very different from lookup fields) to use for selecting campus, wing, and roomtype from combo boxes on your form. For instance, a RoomType table may be simply a listing of RoomTypes. Make a query based on this table (sorted by RoomType), and use the query as the Row Source for a RoomType combo box on your Keys form. tblKeysEmployees Key ID- composite key(FK to tblKeys)- text EmployeeID-composite key (FK to tblEmployees)- Autonumber EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign key field, so it cannot be assigned automatically. Rather, it gets its value from the parent table when a record is created. The two fields together can be a composite PK for this table, but are not by themselves composite keys. tblKeysRequests Request ID-Autonumber Key ID (FK to tblKeys)- text Emp. ID Rm. number- text--I think there is a problem here. This info is part of tblrooms. Rm. phone- text Date Requested- Date/time Date Recvd.- Date/time Date Issued- Date/time tblRooms Key ID (PK)-text Room number- text Remarks-text KeyID as the PK of tblRooms could be confusing. tblSubs Sub ID (PK)-Autonumber SubLN-text SubFN-text MI-text SubPhone-text Key ID (FK to tblKeys) Date Issued-Date/Time Date Returned-Date/Time This table's purpose is unclear. If you're still reading, I have the following questions: 1. How many fields are too many in a table? I understand that Access will accept up to 255. I read a post that suggested that 20-30 fields may indicate there may be a normalization problem. I'm wondering about tblEmployees where there are approx. 25 fields. No problem. More than 30 fields could suggest normalization problems, but it not an invariable rule. If the SchoolData fields are not all filled in for every record it could be that SchoolData could be in a separate table. Nothing else stands out much. 2.tblRooms has a field for Rm.#; the problem is that not all rooms have a #. Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks to tblRooms because I may have to describe a location rather than a room #. Will there be a problem with this method? Only if RoomNumber is a linking field. By the way, I assume you are using the number sign for description, not as a field name. Names should include only letters, number, and underscores. Spaces and other non-alphanumeric characters are best avoided. One of the desired reports is a reverse directory by room #/room phone #. The phone # is currently in tblEmployees. Should it be moved to tblRooms? If I leave it where it is, will I be able to update any queries and reports? I've read that multitable queries aren't updateable. We have had mass staff room changes right before the school year begins. It just seems it would be better if the phone # was listed with tblRooms but someone told me that's incorrect. If the phone number is associated with a room it should be part of a room record. Presumably an employee can be associated with several rooms, and vice versa. However, if an employee has an office where only he or she answers the phone, the phone number is associated with the employee. Often in such case the phone number follows the employee if they move to another office. There is no single answer to your question. Your database can be made to accept either a phone associated with a room or with an employee, or both. It does start to become more complex if you have both situations. 3. I forgot to include the following fields: Date assigned, Return Date and Permission to Retain (over the summer) for Emp. Keys. Should this be added to tblKeys or tblEmployees? I was told not to put *anything* into the junction table (tblKeysEmployees) besides the composite key already listed. You can have other fields in a junction table. For instance, the KeysEmployees junction table may include a DateIssued field, which is a property of neither the Employee nor the Key, but rather of this particular key together with this particular employee. If a key may be retained over the summer by one employee, but not another, then this too is a candidate for a field in the junction table. 4.Keys Requests--Somethimes are request will not have a name associated with it. For instance, we need to request a key for the vault. I have not accounted for this situation and don't know how to handle it. If I need to order a sub. key, I can use the name of the permanent staff member associated with that room. What do I do about the vault? Should I enter "vault" in the emp. table w/o any additional data? Vault chould not be in the employee table, but the situation is unclear. Is nobody responsible for the vault key? How will you know who has it? How do you know now? 5. Maybe I should cross this bridge when I get to it but I eventually will get to the point where I will make forms and subforms. There are approx. 400 seperate keys, not including multiple copies of the same key. If I have a combo box, is there a way to filter so that I am not looking at all 400 possiblities at once? Again, I'm not sure what you're asking. You will need forms and subforms as soon as you start doing data entry. It may be best to begin with just tblKeys, tblEmployees, and tblKeysEmployees, just as a test to see how to work with the junction table arrangement. I am so sorry if this is too long and I'm asking too many questions at once. I understand that you are trying to help as many people as possible. I have searched and searched for answers and tried to adapt the customers/orders format as much as possible to my situation but...I need help. -- Aria W. |
#7
|
|||
|
|||
Still Struggling...
Beetle,
(lol) Yes, you have definitely thrown a monkey wrench into the works! You have raised some valid points that I need to take into account. I have thought about what you posted and would like to clarify some points and ask additional questions. tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text Sorry, but the school data belongs in a separate table/tables. With the above structure you will repeatedly be entering the same descriptive data over and over. Additionally, what if an instructor teaches more than one Subject, works in more than one Dept., etc? You will have to enter additional records for the same employee. Does school data need an additional table based solely on instuctors teaching more than one subject? You are correct though. Teachers can teach more than one subject. I only ask because you said, "additionally", which implies that this will need a new table regardless. Other than "Subject", I don't understand how I would be entering the same information again and again. What am I missing? tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text Again, this is wrong. Campus, Wing and RoomType are not attributes of the Key. This table should have RoomID as a foreign key to tblRooms and probably not much else, unless you store some other descriptive information about the key itself, like "color" or something. Think of it this way - you said you don't really track filing cabinet keys so we'll just take rooms into consideration here. A key opens a room. Period. The Room Type and the Wing in which that room is located are attributes of the Room, not the Key. Likewise, the Campus that Wing is located in is an attribute of the Wing (not the Room or the Key). You should have separate tables for Campus' and Wings Point taken; although I don't understand why the campus location is not a room attribute. I need to know where that room or storage room is located(especially since storage rooms don't have room #s). Does it make a difference if there are only 2 campuses? No? So, is it CampusID (FK to tblRooms)? Same for tblWings? Also, I meant to put KeyType (Storage, Classroom, Gate Master, Grand Master, etc.) as a field here. tblRooms Key ID (PK)-text Room number- text Remarks-text This table should have RoomID as a PK (KeyID does not belong here). A room can have many keys, but a key can only open one room, so RoomID goes in tblKeys as a foreign key, not the other way around. Good point; I'll change this. Your second sentence is not exactly true. Just to clarify, key assignments are based on job title and extracurricular duties (coaching, tutoring, special projects, etc.). Masters allow all access per location. So one key may open one or many doors. As I stated previously, you are correct about the room type and wing being attributes of the room. Why not "Campus"? In addition, the stadium and storage rooms do not have a room #, nor does it belong to a wing. Do I need additional tables for this? tblSubs Sub ID (PK)-Autonumber SubLN-text SubFN-text MI-text SubPhone-text Key ID (FK to tblKeys) Date Issued-Date/Time Date Returned-Date/Time IMO this table is unecessary. Whether an employee is "full time" or "temporary" is just an attribute that would go in tblEmployees. The fact that a person may come and go as a member of your staff is irrelevant to the fact that that person was in posession of a certain key at a certain time. The date that a key was issued/returned belongs in tblKeysEmployees. I don't understand. If I add the substitute staff to the our employee table, most of the fields will be empty. They are not required to disclose their address or emergency info. There address is on file with the district. In addition, they are not bound to teach any subject in particular. If there is a request for a P.E. instructor but they normally teach English, they are free to accept the position for the time requested. This is why I am adding a "Preferred Subject" field. I want the sub to enjoy there time teaching and the permanent staff to feel comfortable with whomever is taking over their class. I am often asked for recommendations; I'd like some info to go along with that. If we have a long-term position for a Special Education instructor, I need to know who has that credential so we can move ASAP. Oddly enough, the district doesn't track this info; I've asked. If I have the subs in their own table, I think all the info I need will be together. You might be thinking "great, now I have to add more tables" but keep in mind that in an application like this you will typically have several tables that will basically just be "lookup" tables. They aren't really a big deal to set up. (lol) That is *exactly* what I was thinking! You may also need some additional junction tables, depending on if - as I stated earlier - an instructor can teach more than one subject, work in more than one department, etc. (heavy sigh...as my head hits the desk) Which ones? tblSubjects? For you first app, you didn't exactly pick a simple one, but everyone loves a challenge, right? g Thank you for saying that! I kept wondering,"Why am I having such a hard time with this?" Why am I still on the pen, paper and diagram stage? I like a challenge because it makes success that much sweeter but *come on*...! It makes it difficult to make a move when you are about ready to step off the cliff at every turn. If it weren't for this discussion group, I would have. Thanks to both you and Bruce for posting your reasons and thought process along with your comments. I don't know about others, but it helps me to follow along. I t takes an extra measure of patience that I appreciate. I am going to rethink my tables and post them again. I hope you will take a look and tell me what you think. -- Aria W. "Beetle" wrote: Hate to throw in the monkey wrench, but I'm going to have to disagree with some of what's been posted (comments inline). tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text Sorry, but the school data belongs in a separate table/tables. With the above structure you will repeatedly be entering the same descriptive data over and over. Additionally, what if an instructor teaches more than one Subject, works in more than one Dept., etc? You will have to enter additional records for the same employee. tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text Again, this is wrong. Campus, Wing and RoomType are not attributes of the Key. This table should have RoomID as a foreign key to tblRooms and probably not much else, unless you store some other descriptive information about the key itself, like "color" or something. Think of it this way - you said you don't really track filing cabinet keys so we'll just take rooms into consideration here. A key opens a room. Period. The Room Type and the Wing in which that room is located are attributes of the Room, not the Key. Likewise, the Campus that Wing is located in is an attribute of the Wing (not the Room or the Key). You should have separate tables for Campus' and Wings tblRooms Key ID (PK)-text Room number- text Remarks-text This table should have RoomID as a PK (KeyID does not belong here). A room can have many keys, but a key can only open one room, so RoomID goes in tblKeys as a foreign key, not the other way around. tblSubs Sub ID (PK)-Autonumber SubLN-text SubFN-text MI-text SubPhone-text Key ID (FK to tblKeys) Date Issued-Date/Time Date Returned-Date/Time IMO this table is unecessary. Whether an employee is "full time" or "temporary" is just an attribute that would go in tblEmployees. The fact that a person may come and go as a member of your staff is irrelevant to the fact that that person was in posession of a certain key at a certain time. The date that a key was issued/returned belongs in tblKeysEmployees. You might be thinking "great, now I have to add more tables" but keep in mind that in an application like this you will typically have several tables that will basically just be "lookup" tables. They aren't really a big deal to set up. Just from the little I know about your app, I would say you would probably have the following tables that would be "lookup" tables; tblClassifications tblDepartments tblSubjects tblCampuses tblWings tblRoomTypes You may also need some additional junction tables, depending on if - as I stated earlier - an instructor can teach more than one subject, work in more than one department, etc. For you first app, you didn't exactly pick a simple one, but everyone loves a challenge, right? g Good luck and welcome to the world of Access. -- _________ Sean Bailey "Aria" wrote: Bruce, First, I would really like to thank you for responding. I need to clarify some of the information that seems to be confusing. tblSubs is a table for temporary, roving district staff. They may be employed at any district site anywhere from a couple of hours to long-term(months). They need access to their assignment location and if they are certificated, temporary passwords for certain reports. My comments are as follows: tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text I ws going to say that SchoolData and PersonalInfo should be broken into several fields each, but later you said something about the number of fields in the table, so it seems the way you have written it here is a sort of shorthand, and that you have separate fields as needed. I think I'm fairly comfortable with this table. I just thought maybe it needed to be broken down (not that I'm looking for another table to put in). Yes, the fields are seperate. tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text One lock could have several keys (one for each of several people). This suggests you need a Locks table, separate from the Keys table. Each lock could have several keys, so there is a one-to-many relationship between locks and keys. Since a filing cabinet could be moved from one room to another, the correlation between locks and rooms may need to be flexible. One approach to the Locks table would be to have fields for Campus, Wing, and Room. You could query the table for locks that go with a particular campus, building, wing, and room, or for all of the locks for a campus, or all of the locks for a building, etc. You may want to build some lookup tables (very different from lookup fields) to use for selecting campus, wing, and roomtype from combo boxes on your form. For instance, a RoomType table may be simply a listing of RoomTypes. Make a query based on this table (sorted by RoomType), and use the query as the Row Source for a RoomType combo box on your Keys form. Now, this is where you lose me. I don't understand the purpose of a Locks table. Isn't that what the keys table is for? We are only interested in the locks as far as what key will staff to gain access to the room, gate, stadium, etc. We don't normally track file cabinet keys. We may if the contents are extremely confidential. tblKeysEmployees Key ID- composite key(FK to tblKeys)- text EmployeeID-composite key (FK to tblEmployees)- Autonumber EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign key field, so it cannot be assigned automatically. Rather, it gets its value from the parent table when a record is created. The two fields together can be a composite PK for this table, but are not by themselves composite keys. Sorry; I dropped the ball on this one. This *is* long interger. I forgot to make the change in designation from my original. tblRooms Key ID (PK)-text Room number- text Remarks-text KeyID as the PK of tblRooms could be confusing. Again, you lost me on this one. Why is the KeyID confusing? It relates to the room; knowing the key ID will allow you access. My thinking was that if I could do a parameter query (is that what I would want?) by room, I would know which key will unlock it. Then I could make a key request or whatever else needs to be done. tblSubs Sub ID (PK)-Autonumber SubLN-text SubFN-text MI-text SubPhone-text Key ID (FK to tblKeys) Date Issued-Date/Time Date Returned-Date/Time This table's purpose is unclear. I'm sorry. I should have explained in the beginning. Like I stated, this is for temporary, roving staff. We need Access only in relation to keys for this group. We have other applications for the rest of the information we need. 1. How many fields are too many in a table? I understand that Access will accept up to 255. I read a post that suggested that 20-30 fields may indicate there may be a normalization problem. I'm wondering about tblEmployees where there are approx. 25 fields. No problem. More than 30 fields could suggest normalization problems, but it not an invariable rule. If the SchoolData fields are not all filled in for every record it could be that SchoolData could be in a separate table. Nothing else stands out much. All the fields relating to school data wil be filled in, so I guess we're good here. 2.tblRooms has a field for Rm.#; the problem is that not all rooms have a #. Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks to tblRooms because I may have to describe a location rather than a room #. Will there be a problem with this method? Only if RoomNumber is a linking field. By the way, I assume you are using the number sign for description, not as a field name. Names should include only letters, number, and underscores. Spaces and other non-alphanumeric characters are best avoided. Oh my gosh! How many times have I read the same thing and because I *knew* my fields weren't like that, moved on. Well, I just wanted to double check and lo and behold, there it was; a # sign in my field name. Thanks for the heads up! One of the desired reports is a reverse directory by room #/room phone #. The phone # is currently in tblEmployees. Should it be moved to tblRooms? If I leave it where it is, will I be able to update any queries and reports? I've read that multitable queries aren't updateable. We have had mass staff room changes right before the school year begins. It just seems it would be better if the phone # was listed with tblRooms but someone told me that's incorrect. If the phone number is associated with a room it should be part of a room record. Presumably an employee can be associated with several rooms, and vice versa. However, if an employee has an office where only he or she answers the phone, the phone number is associated with the employee. Often in such case the phone number follows the employee if they move to another office. There is no single answer to your question. Your database can be made to accept either a phone associated with a room or with an employee, or both. It does start to become more complex if you have both situations. Well, for classroom staff, if they have to change rooms (happens every year, so I have no illusions here), they switch to the new rooms' phone #. For office staff, it could go either way. What do I need to do if anything? 3. I forgot to include the following fields: Date assigned, Return Date and Permission to Retain (over the summer) for Emp. Keys. Should this be added to tblKeys or tblEmployees? I was told not to put *anything* into the junction table (tblKeysEmployees) besides the composite key already listed. You can have other fields in a junction table. For instance, the KeysEmployees junction table may include a DateIssued field, which is a property of neither the Employee nor the Key, but rather of this particular key together with this particular employee. If a key may be retained over the summer by one employee, but not another, then this too is a candidate for a field in the junction table. Your statements are true enough; only the master keys are coded to a particular employee. Classroom keys are a little more generic in that anyone who works out of that classroom or office will have the same key. O.k., I'll put the new fields here. 4.Keys Requests--Sometimes a request will not have a name associated with it. For instance, we need to request a key for the vault. I have not accounted for this situation and don't know how to handle it. If I need to order a sub. key, I can use the name of the permanent staff member associated with that room. What do I do about the vault? Should I enter "vault" in the emp. table w/o any additional data? Vault chould not be in the employee table, but the situation is unclear. Is nobody responsible for the vault key? How will you know who has it? How do you know now? Every key available on both campuses should be in the vault. Theoretically, vault keys remain in the vault. I guess I'm responsible for those keys but anyone who has the combination can remove a key without my knowledge. I guess I could list myself and under Remarks indicate it's a vault request. 5. Maybe I should cross this bridge when I get to it but I eventually will get to the point where I will make forms and subforms. There are approx. 400 seperate keys, not including multiple copies of the same key. If I have a combo box, is there a way to filter so that I am not looking at all 400 possiblities at once? Again, I'm not sure what you're asking. You will need forms and subforms as soon as you start doing data entry. It may be best to begin with just |
#8
|
|||
|
|||
Still Struggling...
Sorry if my reply came off kind of negative. I didn't mean to be.
Sorry, but the school data belongs in a separate table/tables. With the above structure you will repeatedly be entering the same descriptive data over and over. Additionally, what if an instructor teaches more than one Subject, works in more than one Dept., etc? You will have to enter additional records for the same employee. You make some valid points about subjects and departments, and about titles too if somebody holds more than one. I'm not sure exactly what is meant by classification, so I couldn't say if there could be more than one. It depends in part on how they do things. However, the posting was about keys, so correctly or not I chose not to get into a discussion of the Employee table. I sort of knew you were trying to focus more on the keys, so when the OP said they were satisfied with the Employees table I wanted them to at least take another look at it and give it some more thought before moving on. Again, this is wrong. Campus, Wing and RoomType are not attributes of the Key. This table should have RoomID as a foreign key to tblRooms and probably not much else, unless you store some other descriptive information about the key itself, like "color" or something. Think of it this way - you said you don't really track filing cabinet keys so we'll just take rooms into consideration here. A key opens a room. Period. The Room Type and the Wing in which that room is located are attributes of the Room, not the Key. Likewise, the Campus that Wing is located in is an attribute of the Wing (not the Room or the Key). You should have separate tables for Campus' and Wings You may be correct about the RoomID as the FK. However, it isn't necessarily as simple as a key opening a room (period). The building could have a key, for instance, or there could be a padlock or other lock that is not a room (or supply closet) lock. The OP said they don't "really" track file cabinet keys and such, but if they ever have a key to something other than a room provisions will need to be made for that. This is why I suggeested a Locks table. A lock could have several keys. A Locks table would need to specify the lock's location in a way that does not necessarily reference a room (in what room is a building's front door located?). Also, a room having several different locks (in an auditorium, for instance, or a room with an outside door and a hallway door) is not out of the question. I should have re-worded this part of my response, as I can see now that it does not get my point across very well. I actually agree with you about the locks. What I was trying to do was get the OP to realize that things like Room Type, etc. are not attributes of the key. All a key does is - as you correctly point out - open a particular lock. Where that lock is located is an attribute of the lock, not the key. Again, apologies if I came across as if I was saying you were wrong, that wasn't my intention. Regards, Sean Bailey "BruceM" wrote: "Beetle" wrote in message ... Hate to throw in the monkey wrench, but I'm going to have to disagree with some of what's been posted (comments inline). tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text Sorry, but the school data belongs in a separate table/tables. With the above structure you will repeatedly be entering the same descriptive data over and over. Additionally, what if an instructor teaches more than one Subject, works in more than one Dept., etc? You will have to enter additional records for the same employee. You make some valid points about subjects and departments, and about titles too if somebody holds more than one. I'm not sure exactly what is meant by classification, so I couldn't say if there could be more than one. It depends in part on how they do things. However, the posting was about keys, so correctly or not I chose not to get into a discussion of the Employee table. tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text Again, this is wrong. Campus, Wing and RoomType are not attributes of the Key. This table should have RoomID as a foreign key to tblRooms and probably not much else, unless you store some other descriptive information about the key itself, like "color" or something. Think of it this way - you said you don't really track filing cabinet keys so we'll just take rooms into consideration here. A key opens a room. Period. The Room Type and the Wing in which that room is located are attributes of the Room, not the Key. Likewise, the Campus that Wing is located in is an attribute of the Wing (not the Room or the Key). You should have separate tables for Campus' and Wings You may be correct about the RoomID as the FK. However, it isn't necessarily as simple as a key opening a room (period). The building could have a key, for instance, or there could be a padlock or other lock that is not a room (or supply closet) lock. The OP said they don't "really" track file cabinet keys and such, but if they ever have a key to something other than a room provisions will need to be made for that. This is why I suggeested a Locks table. A lock could have several keys. A Locks table would need to specify the lock's location in a way that does not necessarily reference a room (in what room is a building's front door located?). Also, a room having several different locks (in an auditorium, for instance, or a room with an outside door and a hallway door) is not out of the question. tblRooms Key ID (PK)-text Room number- text Remarks-text This table should have RoomID as a PK (KeyID does not belong here). A room can have many keys, but a key can only open one room, so RoomID goes in tblKeys as a foreign key, not the other way around. tblSubs Sub ID (PK)-Autonumber SubLN-text SubFN-text MI-text SubPhone-text Key ID (FK to tblKeys) Date Issued-Date/Time Date Returned-Date/Time IMO this table is unecessary. Whether an employee is "full time" or "temporary" is just an attribute that would go in tblEmployees. The fact that a person may come and go as a member of your staff is irrelevant to the fact that that person was in posession of a certain key at a certain time. The date that a key was issued/returned belongs in tblKeysEmployees. You might be thinking "great, now I have to add more tables" but keep in mind that in an application like this you will typically have several tables that will basically just be "lookup" tables. They aren't really a big deal to set up. Just from the little I know about your app, I would say you would probably have the following tables that would be "lookup" tables; tblClassifications tblDepartments tblSubjects tblCampuses tblWings tblRoomTypes You may also need some additional junction tables, depending on if - as I stated earlier - an instructor can teach more than one subject, work in more than one department, etc. For you first app, you didn't exactly pick a simple one, but everyone loves a challenge, right? g Good luck and welcome to the world of Access. -- _________ Sean Bailey "Aria" wrote: Bruce, First, I would really like to thank you for responding. I need to clarify some of the information that seems to be confusing. tblSubs is a table for temporary, roving district staff. They may be employed at any district site anywhere from a couple of hours to long-term(months). They need access to their assignment location and if they are certificated, temporary passwords for certain reports. My comments are as follows: tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text I ws going to say that SchoolData and PersonalInfo should be broken into several fields each, but later you said something about the number of fields in the table, so it seems the way you have written it here is a sort of shorthand, and that you have separate fields as needed. I think I'm fairly comfortable with this table. I just thought maybe it needed to be broken down (not that I'm looking for another table to put in). Yes, the fields are seperate. tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text One lock could have several keys (one for each of several people). This suggests you need a Locks table, separate from the Keys table. Each lock could have several keys, so there is a one-to-many relationship between locks and keys. Since a filing cabinet could be moved from one room to another, the correlation between locks and rooms may need to be flexible. One approach to the Locks table would be to have fields for Campus, Wing, and Room. You could query the table for locks that go with a particular campus, building, wing, and room, or for all of the locks for a campus, or all of the locks for a building, etc. You may want to build some lookup tables (very different from lookup fields) to use for selecting campus, wing, and roomtype from combo boxes on your form. For instance, a RoomType table may be simply a listing of RoomTypes. Make a query based on this table (sorted by RoomType), and use the query as the Row Source for a RoomType combo box on your Keys form. Now, this is where you lose me. I don't understand the purpose of a Locks table. Isn't that what the keys table is for? We are only interested in the locks as far as what key will staff to gain access to the room, gate, stadium, etc. We don't normally track file cabinet keys. We may if the contents are extremely confidential. tblKeysEmployees Key ID- composite key(FK to tblKeys)- text EmployeeID-composite key (FK to tblEmployees)- Autonumber EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign key field, so it cannot be assigned automatically. Rather, it gets its value from the parent table when a record is created. The two fields together can be a composite PK for this table, but are not by themselves composite keys. Sorry; I dropped the ball on this one. This *is* long interger. I forgot to make the change in designation from my original. tblRooms Key ID (PK)-text Room number- text Remarks-text KeyID as the PK of tblRooms could be confusing. Again, you lost me on this one. Why is the KeyID confusing? It relates to the room; knowing the key ID will allow you access. My thinking was that if I could do a parameter query (is that what I would want?) by room, I would know which key will unlock it. Then I could make a key request or whatever else needs to be done. tblSubs Sub ID (PK)-Autonumber SubLN-text SubFN-text MI-text SubPhone-text Key ID (FK to tblKeys) Date Issued-Date/Time Date Returned-Date/Time This table's purpose is unclear. I'm sorry. I should have explained in the beginning. Like I stated, this is for temporary, roving staff. We need Access only in relation to keys for this group. We have other applications for the rest of the information we need. 1. How many fields are too many in a table? I understand that Access will accept up to 255. I read a post that suggested that 20-30 fields may indicate there may be a normalization problem. I'm wondering about tblEmployees where there are approx. 25 fields. No problem. More than 30 fields could suggest normalization problems, but it not an invariable rule. If the SchoolData fields are not all filled in for every record it could be that SchoolData could be in a separate table. Nothing else stands out much. All the fields relating to school data wil be filled in, so I guess we're good here. 2.tblRooms has a field for Rm.#; the problem is that not all rooms have a #. Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks to tblRooms because I may have to describe a location rather than a room #. Will there be a problem with this method? Only if RoomNumber is a linking field. By the way, I assume you are using the number sign for description, not as a field name. Names should include only letters, number, and underscores. Spaces and other non-alphanumeric characters are best avoided. Oh my gosh! How many times have I read the same thing and because I *knew* my fields weren't like that, moved on. Well, I just wanted to double check and lo and behold, there it was; a # sign in my field name. Thanks for the heads up! One of the desired reports is a reverse directory by room #/room phone #. The phone # is currently in tblEmployees. Should it be moved to tblRooms? If I leave it where it is, will I be able to update any queries and reports? I've read that multitable queries aren't updateable. We have had mass staff room changes right before the school year begins. It just seems it would be better if the phone # was listed with tblRooms but someone told me that's incorrect. If the phone number is associated with a room it should be part of a room record. Presumably an employee can be associated with several rooms, and |
#9
|
|||
|
|||
Still Struggling...
The curious thing was that you were replying to me without including my
wording, but in any case no offense taken. I see from another posting that the OP has recognized some rethinking of the Employee table may be in order. I built an Employee table for one application, then ended up using it for others, so I can attest that a well-designed table is a worthwhile investment of time. I say this because my first attempt was not as well-designed as it might have been, so other applications had to be updated in some cases. Other comments in the OP's most recent reply show a growing realization about other design elements. It was certainly a lot of project for a first attempt. I expect the full extent wasn't really known at the time. "Beetle" wrote in message ... Sorry if my reply came off kind of negative. I didn't mean to be. Sorry, but the school data belongs in a separate table/tables. With the above structure you will repeatedly be entering the same descriptive data over and over. Additionally, what if an instructor teaches more than one Subject, works in more than one Dept., etc? You will have to enter additional records for the same employee. You make some valid points about subjects and departments, and about titles too if somebody holds more than one. I'm not sure exactly what is meant by classification, so I couldn't say if there could be more than one. It depends in part on how they do things. However, the posting was about keys, so correctly or not I chose not to get into a discussion of the Employee table. I sort of knew you were trying to focus more on the keys, so when the OP said they were satisfied with the Employees table I wanted them to at least take another look at it and give it some more thought before moving on. Again, this is wrong. Campus, Wing and RoomType are not attributes of the Key. This table should have RoomID as a foreign key to tblRooms and probably not much else, unless you store some other descriptive information about the key itself, like "color" or something. Think of it this way - you said you don't really track filing cabinet keys so we'll just take rooms into consideration here. A key opens a room. Period. The Room Type and the Wing in which that room is located are attributes of the Room, not the Key. Likewise, the Campus that Wing is located in is an attribute of the Wing (not the Room or the Key). You should have separate tables for Campus' and Wings You may be correct about the RoomID as the FK. However, it isn't necessarily as simple as a key opening a room (period). The building could have a key, for instance, or there could be a padlock or other lock that is not a room (or supply closet) lock. The OP said they don't "really" track file cabinet keys and such, but if they ever have a key to something other than a room provisions will need to be made for that. This is why I suggeested a Locks table. A lock could have several keys. A Locks table would need to specify the lock's location in a way that does not necessarily reference a room (in what room is a building's front door located?). Also, a room having several different locks (in an auditorium, for instance, or a room with an outside door and a hallway door) is not out of the question. I should have re-worded this part of my response, as I can see now that it does not get my point across very well. I actually agree with you about the locks. What I was trying to do was get the OP to realize that things like Room Type, etc. are not attributes of the key. All a key does is - as you correctly point out - open a particular lock. Where that lock is located is an attribute of the lock, not the key. Again, apologies if I came across as if I was saying you were wrong, that wasn't my intention. Regards, Sean Bailey "BruceM" wrote: "Beetle" wrote in message ... Hate to throw in the monkey wrench, but I'm going to have to disagree with some of what's been posted (comments inline). tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text Sorry, but the school data belongs in a separate table/tables. With the above structure you will repeatedly be entering the same descriptive data over and over. Additionally, what if an instructor teaches more than one Subject, works in more than one Dept., etc? You will have to enter additional records for the same employee. You make some valid points about subjects and departments, and about titles too if somebody holds more than one. I'm not sure exactly what is meant by classification, so I couldn't say if there could be more than one. It depends in part on how they do things. However, the posting was about keys, so correctly or not I chose not to get into a discussion of the Employee table. tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text Again, this is wrong. Campus, Wing and RoomType are not attributes of the Key. This table should have RoomID as a foreign key to tblRooms and probably not much else, unless you store some other descriptive information about the key itself, like "color" or something. Think of it this way - you said you don't really track filing cabinet keys so we'll just take rooms into consideration here. A key opens a room. Period. The Room Type and the Wing in which that room is located are attributes of the Room, not the Key. Likewise, the Campus that Wing is located in is an attribute of the Wing (not the Room or the Key). You should have separate tables for Campus' and Wings You may be correct about the RoomID as the FK. However, it isn't necessarily as simple as a key opening a room (period). The building could have a key, for instance, or there could be a padlock or other lock that is not a room (or supply closet) lock. The OP said they don't "really" track file cabinet keys and such, but if they ever have a key to something other than a room provisions will need to be made for that. This is why I suggeested a Locks table. A lock could have several keys. A Locks table would need to specify the lock's location in a way that does not necessarily reference a room (in what room is a building's front door located?). Also, a room having several different locks (in an auditorium, for instance, or a room with an outside door and a hallway door) is not out of the question. tblRooms Key ID (PK)-text Room number- text Remarks-text This table should have RoomID as a PK (KeyID does not belong here). A room can have many keys, but a key can only open one room, so RoomID goes in tblKeys as a foreign key, not the other way around. tblSubs Sub ID (PK)-Autonumber SubLN-text SubFN-text MI-text SubPhone-text Key ID (FK to tblKeys) Date Issued-Date/Time Date Returned-Date/Time IMO this table is unecessary. Whether an employee is "full time" or "temporary" is just an attribute that would go in tblEmployees. The fact that a person may come and go as a member of your staff is irrelevant to the fact that that person was in posession of a certain key at a certain time. The date that a key was issued/returned belongs in tblKeysEmployees. You might be thinking "great, now I have to add more tables" but keep in mind that in an application like this you will typically have several tables that will basically just be "lookup" tables. They aren't really a big deal to set up. Just from the little I know about your app, I would say you would probably have the following tables that would be "lookup" tables; tblClassifications tblDepartments tblSubjects tblCampuses tblWings tblRoomTypes You may also need some additional junction tables, depending on if - as I stated earlier - an instructor can teach more than one subject, work in more than one department, etc. For you first app, you didn't exactly pick a simple one, but everyone loves a challenge, right? g Good luck and welcome to the world of Access. -- _________ Sean Bailey "Aria" wrote: Bruce, First, I would really like to thank you for responding. I need to clarify some of the information that seems to be confusing. tblSubs is a table for temporary, roving district staff. They may be employed at any district site anywhere from a couple of hours to long-term(months). They need access to their assignment location and if they are certificated, temporary passwords for certain reports. My comments are as follows: tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text I ws going to say that SchoolData and PersonalInfo should be broken into several fields each, but later you said something about the number of fields in the table, so it seems the way you have written it here is a sort of shorthand, and that you have separate fields as needed. I think I'm fairly comfortable with this table. I just thought maybe it needed to be broken down (not that I'm looking for another table to put in). Yes, the fields are seperate. tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text One lock could have several keys (one for each of several people). This suggests you need a Locks table, separate from the Keys table. Each lock could have several keys, so there is a one-to-many relationship between locks and keys. Since a filing cabinet could be moved from one room to another, the correlation between locks and rooms may need to be flexible. One approach to the Locks table would be to have fields for Campus, Wing, and Room. You could query the table for locks that go with a particular campus, building, wing, and room, or for all of the locks for a campus, or all of the locks for a building, etc. You may want to build some lookup tables (very different from lookup fields) to use for selecting campus, wing, and roomtype from combo boxes on your form. For instance, a RoomType table may be simply a listing of RoomTypes. Make a query based on this table (sorted by RoomType), and use the query as the Row Source for a RoomType combo box on your Keys form. Now, this is where you lose me. I don't understand the purpose of a Locks table. Isn't that what the keys table is for? We are only interested in the locks as far as what key will staff to gain access to the room, gate, stadium, etc. We don't normally track file cabinet keys. We may if the contents are extremely confidential. tblKeysEmployees Key ID- composite key(FK to tblKeys)- text EmployeeID-composite key (FK to tblEmployees)- Autonumber EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign key field, so it cannot be assigned automatically. Rather, it gets its value from the parent table when a record is created. The two fields together can be a composite PK for this table, but are not by themselves composite keys. Sorry; I dropped the ball on this one. This *is* long interger. I forgot to make the change in designation from my original. tblRooms Key ID (PK)-text Room number- text Remarks-text KeyID as the PK of tblRooms could be confusing. Again, you lost me on this one. Why is the KeyID confusing? It relates to the room; knowing the key ID will allow you access. My thinking was that if I could do a parameter query (is that what I would want?) by room, I would know which key will unlock it. Then I could make a key request or whatever else needs to be done. tblSubs Sub ID (PK)-Autonumber SubLN-text SubFN-text MI-text SubPhone-text Key ID (FK to tblKeys) Date Issued-Date/Time Date Returned-Date/Time This table's purpose is unclear. I'm sorry. I should have explained in the beginning. Like I stated, this is for temporary, roving staff. We need Access only in relation to keys for this group. We have other applications for the rest of the information we need. 1. How many fields are too many in a table? I understand that Access will accept up to 255. I read a post that suggested that 20-30 fields may indicate there may be a normalization problem. I'm wondering about tblEmployees where there are approx. 25 fields. No problem. More than 30 fields could suggest normalization problems, but it not an invariable rule. If the SchoolData fields are not all filled in for every record it could be that SchoolData could be in a separate table. Nothing else stands out much. All the fields relating to school data wil be filled in, so I guess we're good here. 2.tblRooms has a field for Rm.#; the problem is that not all rooms have a #. Ex.-Storage rms., clinic, auditorium, etc. This is why I added remarks to tblRooms because I may have to describe a location rather than a room #. Will there be a problem with this method? Only if RoomNumber is a linking field. By the way, I assume you are using the number sign for description, not as a field name. Names should include only letters, number, and underscores. Spaces and other non-alphanumeric characters are best avoided. Oh my gosh! How many times have I read the same thing and because I *knew* my fields weren't like that, moved on. Well, I just wanted to double check and lo and behold, there it was; a # sign in my field name. Thanks for the heads up! One of the desired reports is a reverse directory by room #/room phone #. The phone # is currently in tblEmployees. Should it be moved to tblRooms? If I leave it where it is, will I be able to update any queries and reports? I've read that multitable queries aren't updateable. We have had mass staff room changes right before the school year begins. It just seems it would be better if the phone # was listed with tblRooms but someone told me that's incorrect. If the phone number is associated with a room it should be part of a room record. Presumably an employee can be associated with several rooms, and |
#10
|
|||
|
|||
Still Struggling...
Does school data need an additional table based solely on instuctors
teaching more than one subject? You are correct though. Teachers can teach more than one subject. I only ask because you said, "additionally", which implies that this will need a new table regardless. Other than "Subject", I don't understand how I would be entering the same information again and again. What am I missing? In my opinion it should be in separate tables regardless. I don't know how Classification and Title relate to your employees, so for now I'll use Dept. Name and Subject as examples. As your table is currently designed, each time you enter an employee record the user will have to manually type in the Dept. Name and Subject. This is not only extra work, it also invites spelling errors and invalid data in your table. The correct way is to have separate "lookup" tables that store all possible Departments and Subjects. These would be very simple tables that would likely just have a few fields like; DeptID DeptName SubjectID SubjectName Then you would use DeptID and SubjectID as foreign keys in other tables. In your data entry forms, you would typically use combo boxes to allow users to select the correct Dept./Subject from the list of choices. Which tables should DeptID/SubjectID go in? Good question. I think this is a very important part of your application that you have not completely defined yet. In a relational database like Access, it is not only imerative that you determine what relationships you have, but what *type* each relationship is. Is it One-to-Many? Is it Many-to-Many? If it is 1:m, then the PK field from the "One" side table goes in the "Many" side table as a foreign key. If it is m:m, then you need a third (junction) table to define the relationship, like your tblKeysEmployees. You said a teacher can teach more than one subject, and, presumably, a subject can be taught by more than one teacher, so this relationship is m:m. Therefore, you not only need another table to define the list of available subjects, but also a junction table to define the relationship. Again, this is wrong. Campus, Wing and RoomType are not attributes of the Key. This table should have RoomID as a foreign key to tblRooms and probably not much else, unless you store some other descriptive information about the key itself, like "color" or something. Think of it this way - you said you don't really track filing cabinet keys so we'll just take rooms into consideration here. A key opens a room. Period. The Room Type and the Wing in which that room is located are attributes of the Room, not the Key. Likewise, the Campus that Wing is located in is an attribute of the Wing (not the Room or the Key). You should have separate tables for Campus' and Wings Point taken; although I don't understand why the campus location is not a room attribute. I need to know where that room or storage room is located(especially since storage rooms don't have room #s). Does it make a difference if there are only 2 campuses? No? So, is it CampusID (FK to tblRooms)? Same for tblWings? Also, I meant to put KeyType (Storage, Classroom, Gate Master, Grand Master, etc.) as a field here. As I stated in my reply to Bruce, I don't think I worded this part of my reply very well. I was trying to point out that things like RoomType are not attributes of a key. I actually think bruce is right about this. When you break things down to lowest common denominator, all a key does is open a lock (or many locks if it is a master key). Where that lock is located is an attribute of the lock, not the key. As far as whether the Campus is an attribute of the Room? Maybe it should be, I don't know enough about your app to say for sure. My train of thought in my previous reply wasa basically the following; A Campus would usually encompass more than one building, a building may have more than one wing, and a wing may have more than one room. So, again, if we break it down to LCD (so to speak) a room is only indirectly related to a Campus, it's direct relationship would the wing in which it is located (or perhaps the building if that building has no "wings"). However, as Bruce correctly pointed out, what we are really talking about is locks, so it should probably be broken down to that level. Good point; I'll change this. Your second sentence is not exactly true. Just to clarify, key assignments are based on job title and extracurricular duties (coaching, tutoring, special projects, etc.). Masters allow all access per location. So one key may open one or many doors. I hadn't thought about Master keys. You may want to consider a separate table for those, since they have a different type of relationship with the locks than the regular keys. I don't understand. If I add the substitute staff to the our employee table, most of the fields will be empty. They are not required to disclose their address or emergency info. There address is on file with the district. In addition, they are not bound to teach any subject in particular. If there is a request for a P.E. instructor but they normally teach English, they are free to accept the position for the time requested. This is why I am adding a "Preferred Subject" field. I want the sub to enjoy there time teaching and the permanent staff to feel comfortable with whomever is taking over their class. I am often asked for recommendations; I'd like some info to go along with that. If we have a long-term position for a Special Education instructor, I need to know who has that credential so we can move ASAP. Oddly enough, the district doesn't track this info; I've asked. If I have the subs in their own table, I think all the info I need will be together. OK, I can see where that is a little tricky. Still, I think I would just list them in the employee table and live with a few empty address fields. If you have them in a separate table, then you're going to have to add another FK field to your tblKeysEmployees, so either way you end up with empty fields in one of your tables. Thank you for saying that! I kept wondering,"Why am I having such a hard time with this?" Why am I still on the pen, paper and diagram stage? I like a challenge because it makes success that much sweeter but *come on*...! It makes it difficult to make a move when you are about ready to step off the cliff at every turn. If it weren't for this discussion group, I would have. Thanks to both you and Bruce for posting your reasons and thought process along with your comments. I don't know about others, but it helps me to follow along. I t takes an extra measure of patience that I appreciate. I am going to rethink my tables and post them again. I hope you will take a look and tell me what you think. Yeah, you kind of jumped right into the deep end of the pool, but hopefully with help from the group here you'll get it all sorted out. You'll get differing opinions from people too, so sometimes you just have to absorb the different ideas and then decide what you think will work best for you. -- _________ Sean Bailey "Aria" wrote: Beetle, (lol) Yes, you have definitely thrown a monkey wrench into the works! You have raised some valid points that I need to take into account. I have thought about what you posted and would like to clarify some points and ask additional questions. tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text Sorry, but the school data belongs in a separate table/tables. With the above structure you will repeatedly be entering the same descriptive data over and over. Additionally, what if an instructor teaches more than one Subject, works in more than one Dept., etc? You will have to enter additional records for the same employee. Does school data need an additional table based solely on instuctors teaching more than one subject? You are correct though. Teachers can teach more than one subject. I only ask because you said, "additionally", which implies that this will need a new table regardless. Other than "Subject", I don't understand how I would be entering the same information again and again. What am I missing? tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text Again, this is wrong. Campus, Wing and RoomType are not attributes of the Key. This table should have RoomID as a foreign key to tblRooms and probably not much else, unless you store some other descriptive information about the key itself, like "color" or something. Think of it this way - you said you don't really track filing cabinet keys so we'll just take rooms into consideration here. A key opens a room. Period. The Room Type and the Wing in which that room is located are attributes of the Room, not the Key. Likewise, the Campus that Wing is located in is an attribute of the Wing (not the Room or the Key). You should have separate tables for Campus' and Wings Point taken; although I don't understand why the campus location is not a room attribute. I need to know where that room or storage room is located(especially since storage rooms don't have room #s). Does it make a difference if there are only 2 campuses? No? So, is it CampusID (FK to tblRooms)? Same for tblWings? Also, I meant to put KeyType (Storage, Classroom, Gate Master, Grand Master, etc.) as a field here. tblRooms Key ID (PK)-text Room number- text Remarks-text This table should have RoomID as a PK (KeyID does not belong here). A room can have many keys, but a key can only open one room, so RoomID goes in tblKeys as a foreign key, not the other way around. Good point; I'll change this. Your second sentence is not exactly true. Just to clarify, key assignments are based on job title and extracurricular duties (coaching, tutoring, special projects, etc.). Masters allow all access per location. So one key may open one or many doors. As I stated previously, you are correct about the room type and wing being attributes of the room. Why not "Campus"? In addition, the stadium and storage rooms do not have a room #, nor does it belong to a wing. Do I need additional tables for this? tblSubs Sub ID (PK)-Autonumber SubLN-text SubFN-text MI-text SubPhone-text Key ID (FK to tblKeys) Date Issued-Date/Time Date Returned-Date/Time IMO this table is unecessary. Whether an employee is "full time" or "temporary" is just an attribute that would go in tblEmployees. The fact that a person may come and go as a member of your staff is irrelevant to the fact that that person was in posession of a certain key at a certain time. The date that a key was issued/returned belongs in tblKeysEmployees. I don't understand. If I add the substitute staff to the our employee table, most of the fields will be empty. They are not required to disclose their address or emergency info. There address is on file with the district. In addition, they are not bound to teach any subject in particular. If there is a request for a P.E. instructor but they normally teach English, they are free to accept the position for the time requested. This is why I am adding a "Preferred Subject" field. I want the sub to enjoy there time teaching and the permanent staff to feel comfortable with whomever is taking over their class. I am often asked for recommendations; I'd like some info to go along with that. If we have a long-term position for a Special Education instructor, I need to know who has that credential so we can move ASAP. Oddly enough, the district doesn't track this info; I've asked. If I have the subs in their own table, I think all the info I need will be together. You might be thinking "great, now I have to add more tables" but keep in mind that in an application like this you will typically have several tables that will basically just be "lookup" tables. They aren't really a big deal to set up. (lol) That is *exactly* what I was thinking! You may also need some additional junction tables, depending on if - as I stated earlier - an instructor can teach more than one subject, work in more than one department, etc. (heavy sigh...as my head hits the desk) Which ones? tblSubjects? For you first app, you didn't exactly pick a simple one, but everyone loves a challenge, right? g Thank you for saying that! I kept wondering,"Why am I having such a hard time with this?" Why am I still on the pen, paper and diagram stage? I like a challenge because it makes success that much sweeter but *come on*...! It makes it difficult to make a move when you are about ready to step off the cliff at every turn. If it weren't for this discussion group, I would have. Thanks to both you and Bruce for posting your reasons and thought process along with your comments. I don't know about others, but it helps me to follow along. I t takes an extra measure of patience that I appreciate. I am going to rethink my tables and post them again. I hope you will take a look and tell me what you think. -- Aria W. "Beetle" wrote: Hate to throw in the monkey wrench, but I'm going to have to disagree with some of what's been posted (comments inline). tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text Sorry, but the school data belongs in a separate table/tables. With the above structure you will repeatedly be entering the same descriptive data over and over. Additionally, what if an instructor teaches more than one Subject, works in more than one Dept., etc? You will have to enter additional records for the same employee. tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text Again, this is wrong. Campus, Wing and RoomType are not attributes of the Key. This table should have RoomID as a foreign key to tblRooms and probably not much else, unless you store some other descriptive information about the key itself, like "color" or something. Think of it this way - you said you don't really track filing cabinet keys so we'll just take rooms into consideration here. A key opens a room. Period. The Room Type and the Wing in which that room is located are attributes of the Room, not the Key. Likewise, the Campus that Wing is located in is an attribute of the Wing (not the Room or the Key). You should have separate tables for Campus' and Wings tblRooms Key ID (PK)-text Room number- text Remarks-text This table should have RoomID as a PK (KeyID does not belong here). A room can have many keys, but a key can only open one room, so RoomID goes in tblKeys as a foreign key, not the other way around. tblSubs Sub ID (PK)-Autonumber SubLN-text SubFN-text MI-text SubPhone-text Key ID (FK to tblKeys) Date Issued-Date/Time Date Returned-Date/Time IMO this table is unecessary. Whether an employee is "full time" or "temporary" is just an attribute that would go in tblEmployees. The fact that a person may come and go as a member of your staff is irrelevant to the fact that that person was in posession of a certain key at a certain time. The date that a key was issued/returned belongs in tblKeysEmployees. You might be thinking "great, now I have to add more tables" but keep in mind that in an application like this you will typically have several tables that will basically just be "lookup" tables. They aren't really a big deal to set up. Just from the little I know about your app, I would say you would probably have the following tables that would be "lookup" tables; tblClassifications tblDepartments tblSubjects tblCampuses tblWings tblRoomTypes You may also need some additional junction tables, depending on if - as I stated earlier - an instructor can teach more than one subject, work in more than one department, etc. For you first app, you didn't exactly pick a simple one, but everyone loves a challenge, right? g Good luck and welcome to the world of Access. -- _________ Sean Bailey "Aria" wrote: Bruce, First, I would really like to thank you for responding. I need to clarify some of the information that seems to be confusing. tblSubs is a table for temporary, roving district staff. They may be employed at any district site anywhere from a couple of hours to long-term(months). They need access to their assignment location and if they are certificated, temporary passwords for certain reports. My comments are as follows: tblEmployees Inactive- Yes/No Date- Date/Time Date Modified- Date/Time Employee ID- (PK) Auto number Long Integer School Data (Classification, Title, Dept. Name, Subject)- text Personal Info (LN FN MI etc.)- text Emergency Info - text I ws going to say that SchoolData and PersonalInfo should be broken into several fields each, but later you said something about the number of fields in the table, so it seems the way you have written it here is a sort of shorthand, and that you have separate fields as needed. I think I'm fairly comfortable with this table. I just thought maybe it needed to be broken down (not that I'm looking for another table to put in). Yes, the fields are seperate. tblKeys Key ID (PK)- text Campus- text Wing- text RoomType(Classroom, Auditorium, Grand Master, etc.)- text One lock could have several keys (one for each of several people). This suggests you need a Locks table, separate from the Keys table. Each lock could have several keys, so there is a one-to-many relationship between locks and keys. Since a filing cabinet could be moved from one room to another, the correlation between locks and rooms may need to be flexible. One approach to the Locks table would be to have fields for Campus, Wing, and Room. You could query the table for locks that go with a particular campus, building, wing, and room, or for all of the locks for a campus, or all of the locks for a building, etc. You may want to build some lookup tables (very different from lookup fields) to use for selecting campus, wing, and roomtype from combo boxes on your form. For instance, a RoomType table may be simply a listing of RoomTypes. Make a query based on this table (sorted by RoomType), and use the query as the Row Source for a RoomType combo box on your Keys form. Now, this is where you lose me. I don't understand the purpose of a Locks table. Isn't that what the keys table is for? We are only interested in the locks as far as what key will staff to gain access to the room, gate, stadium, etc. We don't normally track file cabinet keys. We may if the contents are extremely confidential. tblKeysEmployees Key ID- composite key(FK to tblKeys)- text EmployeeID-composite key (FK to tblEmployees)- Autonumber EmployeeID must be Number (Long Integer), not Autonumber. It is a foreign key field, so it cannot be assigned automatically. Rather, it gets its value from the parent table when a record is created. The two fields together can be a composite PK for this table, but are not by themselves composite keys. Sorry; I dropped the ball on this one. This *is* long interger. I forgot to make the change in designation from my original. |
Thread Tools | |
Display Modes | |
|
|