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
|
|||
|
|||
Intricate Relationship....
I am envisioning 3 tables - Table 1 containing names of all employees, Table
2 containing the employees with each of the jobs they are trained for, and Table 3 that contains a list of documentation for each job. I understand how to create a relationship between Table 1 and Table 2 as well as the relationship between Table 2 and Table 3. Each year the employees are required to review the documentation for each and every job they are trained to perform. How can I maintain a history of when they've reviewed the documents each year. My only thought so far is that Table 1's structure would be something like EmpName JobName Document ReviewDate Downside of this is Job #1 might have 17 documents associated w/it so I'd have to re-enter the JobName 17 times for this employee. That's just one job. Some employees are trained on dozens of jobs so my data entry requirements would be a nightmare. The pupose of creeating a relationship btw Table 2 and Table 3 is to eliminate that but I can't figure out how to store a historical record of the dates each employee reviewed the required documents. Any thoughts? Or is this too complex for ACCESS? Jeff |
#2
|
|||
|
|||
i assume that the documents are directly related to the jobs, as in "one job
may have many documents". and each employee is required to review those documents associated with each job s/he is trained for, once a year. you don't mention if two jobs may have a document in common, or if each document is unique to the job it's associated with - so i'll assume the latter. based on the above, suggest the following tables: tblEmployees EmpID (primary key) FirstName LastName (any other fields that describe an employee) tblJobs JobID (primary key) JobName (any other fields that describe a job) tblJobDocuments DocID (primary key) JobID (foreign key from tblJobs) DocName (any other fields that describe a document) tblEmpJobs EmpJobID (primary key) EmpID (foreign key from tblEmployees) JobID (foreign key from tblJobs) tblDocumentReview ReviewID (primary key) EmpID (foreign key from tblEmployees) DocID (foreign key from tblJobDocuments) ReviewDate in tblDocumentReview, you would have a record for each document reviewed by each employee on each specific date. you're correct, that is a fair amount of data entry, especially if there are a number of documents per job. but data entry is not a consideration when you create tables/relationships; proper table normalization is your goal at that point. when you begin building forms for the actual data entry, you can be very creative in developing an interface to best support the way you need to enter the data, with the minimum time/effort and maximum accuracy. hth "Jeff Harbin" wrote in message ... I am envisioning 3 tables - Table 1 containing names of all employees, Table 2 containing the employees with each of the jobs they are trained for, and Table 3 that contains a list of documentation for each job. I understand how to create a relationship between Table 1 and Table 2 as well as the relationship between Table 2 and Table 3. Each year the employees are required to review the documentation for each and every job they are trained to perform. How can I maintain a history of when they've reviewed the documents each year. My only thought so far is that Table 1's structure would be something like EmpName JobName Document ReviewDate Downside of this is Job #1 might have 17 documents associated w/it so I'd have to re-enter the JobName 17 times for this employee. That's just one job. Some employees are trained on dozens of jobs so my data entry requirements would be a nightmare. The pupose of creeating a relationship btw Table 2 and Table 3 is to eliminate that but I can't figure out how to store a historical record of the dates each employee reviewed the required documents. Any thoughts? Or is this too complex for ACCESS? Jeff |
#3
|
|||
|
|||
tina wrote: tblEmpJobs EmpJobID (primary key) EmpID (foreign key from tblEmployees) JobID (foreign key from tblJobs) I think you need to review your constraints e.g. you have nothing to prevent: INSERT INTO (EmpJobID, EmpID, JobID) VALUES (1,1,1); INSERT INTO (EmpJobID, EmpID, JobID) VALUES (2,1,1); INSERT INTO (EmpJobID, EmpID, JobID) VALUES (3,1,1); .... Jamie. -- |
#4
|
|||
|
|||
you're correct that i didn't include details on table design, such as unique
indexes, or alternate suggestions for primary keys, such as combination primary key instead of surrogate primary key. "onedaywhen" wrote in message ups.com... tina wrote: tblEmpJobs EmpJobID (primary key) EmpID (foreign key from tblEmployees) JobID (foreign key from tblJobs) I think you need to review your constraints e.g. you have nothing to prevent: INSERT INTO (EmpJobID, EmpID, JobID) VALUES (1,1,1); INSERT INTO (EmpJobID, EmpID, JobID) VALUES (2,1,1); INSERT INTO (EmpJobID, EmpID, JobID) VALUES (3,1,1); ... Jamie. -- |
#5
|
|||
|
|||
tina wrote: you're correct that i didn't include details on table design, such as unique indexes, or alternate suggestions for primary keys, such as combination primary key instead of surrogate primary key. So you omitted these and suggested a surrogate primary key because ...? Jamie. -- |
#6
|
|||
|
|||
Jeff's question was about basic table/relationship setup. i gave him an
answer that i felt might give him a starting point for setting up the basic structure. if you want to give him information about combination primary keys, unique indexes, or anything else, i imagine he'll appreciate all the feedback he can get; that's what we're all here for. "onedaywhen" wrote in message oups.com... tina wrote: you're correct that i didn't include details on table design, such as unique indexes, or alternate suggestions for primary keys, such as combination primary key instead of surrogate primary key. So you omitted these and suggested a surrogate primary key because ...? Jamie. -- |
#7
|
|||
|
|||
tina wrote:
Jeff's question was about basic table/relationship setup. i gave him an answer that i felt might give him a starting point for setting up the basic structure. If you were aiming at the basic level, it would have been simpler to omit the 'surrogate key' and use the existing compound key! Jamie. -- |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Deleting a foreign key relationship in SQL | Stevio | Running & Setting Up Queries | 2 | December 22nd, 2004 02:51 PM |
Re-establishing a broken relationship | David McKnight | Database Design | 2 | December 1st, 2004 10:49 AM |
Table Wizard Does Not Set Relationship if Foreign Key and Primary Key Name Do Not Match Exactly in Case. | HDW | Database Design | 3 | October 16th, 2004 03:42 AM |
Setting dual relationship with tool connector | Carlos | Visio | 0 | May 20th, 2004 12:51 AM |