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
|
|||
|
|||
Staff vs. Contact Tables
I'm pretty sure I have a design problem here but can't
seem to see it (although I'm sure it's right in front of my face!)... I have a database that stores library information (location, employees, etc). In this database I've got three tables that are starting to frustrate me: one is a table that contains library information, one contains staff members, and the last contains the people whom are certain contacts for each library. These tables are called Library, Staff and Contacts. The library table has a primary key called library code. Library code is a foreign key in the staff table (indicating where the staff person works primarily) and is also a foreign key in the contacts table (indicating which library they are a contact for). My problem is this: when I try to run a query that pulls library information, staff information (those that work at the library) and contact information (the contact people for that library), I end up losing some data (usually the library code field for staff or contacts). How can I design this better? Contact people are staff members that work at a single library but can be contacts for many libraries. There are also about 5 different contact types (I use a field called contact type in the contact table). Any help would be appreciated!!! Thanks, Kelly |
#2
|
|||
|
|||
Staff vs. Contact Tables
Kelly
You kinda "snuck" a little zinger in there near the end. It sounded like you said that a "contact" was a "staff" member?! If that's the case, they're ALL staff members, right? It sounds like you have Libraries and People, and you have a "Role" (this person is a "staff" type at that library; person B is a "contact" type at library 17, ...) How "welded" are you to your current data structure? -- Good luck Jeff Boyce Access MVP |
#3
|
|||
|
|||
Staff vs. Contact Tables
Not Welded at all!!
It is correct that all contacts are staff members. The part I'm having trouble with is that I need to distinguish who works where and who is a contact for each library. So, even though a Jane Doe is a staff member at Library A she is also a System Administrator (contact type) for Library A as well as Library B, C and maybe Z. I eventually want to create a report that lists each library's information, the contacts for each library (type and name, ie. Sys Admin: Jane Doe), and the staff members at each library (people who work there but are not contacts of any sort). Does that all make sense? Thanks!!! -----Original Message----- Kelly You kinda "snuck" a little zinger in there near the end. It sounded like you said that a "contact" was a "staff" member?! If that's the case, they're ALL staff members, right? It sounds like you have Libraries and People, and you have a "Role" (this person is a "staff" type at that library; person B is a "contact" type at library 17, ...) How "welded" are you to your current data structure? -- Good luck Jeff Boyce Access MVP . |
#4
|
|||
|
|||
Staff vs. Contact Tables
Kelly
It sounds like Jane Doe could have more than one row in the "Role" table I suggested earlier. One row would be: "Jane Doe" "works at" "Library A" "as a staff member" "Jane Doe" "serves" "Library A" "as a System Admin" "Jane Doe" "serves" "Library B" "as a System Admin" If I were setting up a table to handle this, I'd be using ID#s, so it would look more like: 23 1 4 7 23 2 4 10 23 2 5 10 but I'd be able to use a query to join the four other tables and "get" the text back. Here's the structure I've come up with, based on what I understand so far: tblPerson PersonID FName ... tblFacility (in case the "business" is ever broader than "Libraries") FacilityID FacilityName ... tlkpRelationship (a table of lookup values: "works at", "serves", ???) RelationshipID Relationship ... tlkpPosition PositionID Position (e.g., staff member, Administrator, ???) ... trelRole RoleID PersonID RelationshipID FacilityID PositionID BeginDate EndDate ... Does this come close to describing your situation? -- Good luck Jeff Boyce Access MVP |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Auto entering the staff ID | Ant | General Discussion | 1 | June 21st, 2004 06:49 PM |
Mutliple Tables lookup? | Westley | Database Design | 4 | June 15th, 2004 01:07 AM |
searching for "join tables" and "join word tables" | Uncle Bill | Tables | 1 | June 11th, 2004 09:33 PM |
Auto Insertion contact name, company, tel, fax & email from Outlook | Michael | Mailmerge | 1 | June 11th, 2004 01:08 PM |
Display associated contact in tasks list | Chuender | General Discussion | 1 | June 1st, 2004 09:26 PM |