|
| 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
November 16th, 2005, 04:47 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
Table Design Question
I have a DB I use for training records at work. The training records the
employees are trained on are called Standard Operating Procedures (SOPs). The SOPs are placed in categories. There are 4 departments that perform SOP training. The table I enter the SOPs into is called tblSOPs. It consists of the following fields: Field 1- SOPID (a unique number for each SOP - the number cannot be duplicated) Field 2- SOPTitle Field 3- Category (Can be one or up to four categories per SOP - For example: Department A may need to be trained on SOP 123. Department A, B, & C may need to be trained on SOP 234. etc.) I also have a table for categories. (tblCategory) Dept. A has 7 categories Dept. B has 2 categories Dept. C has 7 categories Dept. D has 8 categories This table consists of the following fields: Category DeptID Someone told me, if a SOP (for example) has 4 categories assigned to it, (using my current table structure), enter it 4 times (4 different records) (with different values in the SOPID field), with the same SOPTitle and different categories in each record. I cannot do that because the SOPID cannot be duplicated. I'm confused as to what to do now. MY SOPID has to remain unique. How shoud I change my table structure so I can enter up to 4 categories per SOP? Do you need to see all of my tables in the DB and how they relate to one another to resolve this issue? I really need help with this. If someone has any suggestions, I would really appreciate some help. Thank you, Karen 
|
|
#2
November 16th, 2005, 05:01 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Table Design Question
You will need a new table that has SOPID and Category. Use the two fields to
create a compound key. "Karen" wrote: I have a DB I use for training records at work. The training records the employees are trained on are called Standard Operating Procedures (SOPs). The SOPs are placed in categories. There are 4 departments that perform SOP training. The table I enter the SOPs into is called tblSOPs. It consists of the following fields: Field 1- SOPID (a unique number for each SOP - the number cannot be duplicated) Field 2- SOPTitle Field 3- Category (Can be one or up to four categories per SOP - For example: Department A may need to be trained on SOP 123. Department A, B, & C may need to be trained on SOP 234. etc.) I also have a table for categories. (tblCategory) Dept. A has 7 categories Dept. B has 2 categories Dept. C has 7 categories Dept. D has 8 categories This table consists of the following fields: Category DeptID Someone told me, if a SOP (for example) has 4 categories assigned to it, (using my current table structure), enter it 4 times (4 different records) (with different values in the SOPID field), with the same SOPTitle and different categories in each record. I cannot do that because the SOPID cannot be duplicated. I'm confused as to what to do now. MY SOPID has to remain unique. How shoud I change my table structure so I can enter up to 4 categories per SOP? Do you need to see all of my tables in the DB and how they relate to one another to resolve this issue? I really need help with this. If someone has any suggestions, I would really appreciate some help. Thank you, Karen
|
|
#3
November 16th, 2005, 06:03 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Table Design Question
I do not see how. I am assuming your Category is something like Safety,
Finance, Security, EEO, etc. "Karen" wrote: Can't I just modify the tblCategory? Which consists of Category and DepartmentID field? Thank you "KARL DEWEY" wrote: You will need a new table that has SOPID and Category. Use the two fields to create a compound key. "Karen" wrote: I have a DB I use for training records at work. The training records the employees are trained on are called Standard Operating Procedures (SOPs). The SOPs are placed in categories. There are 4 departments that perform SOP training. The table I enter the SOPs into is called tblSOPs. It consists of the following fields: Field 1- SOPID (a unique number for each SOP - the number cannot be duplicated) Field 2- SOPTitle Field 3- Category (Can be one or up to four categories per SOP - For example: Department A may need to be trained on SOP 123. Department A, B, & C may need to be trained on SOP 234. etc.) I also have a table for categories. (tblCategory) Dept. A has 7 categories Dept. B has 2 categories Dept. C has 7 categories Dept. D has 8 categories This table consists of the following fields: Category DeptID Someone told me, if a SOP (for example) has 4 categories assigned to it, (using my current table structure), enter it 4 times (4 different records) (with different values in the SOPID field), with the same SOPTitle and different categories in each record. I cannot do that because the SOPID cannot be duplicated. I'm confused as to what to do now. MY SOPID has to remain unique. How shoud I change my table structure so I can enter up to 4 categories per SOP? Do you need to see all of my tables in the DB and how they relate to one another to resolve this issue? I really need help with this. If someone has any suggestions, I would really appreciate some help. Thank you, Karen
|
|
#4
November 16th, 2005, 06:28 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Table Design Question
Can't I just modify the tblCategory? Which consists of Category and
DepartmentID field? Thank you "KARL DEWEY" wrote: You will need a new table that has SOPID and Category. Use the two fields to create a compound key. "Karen" wrote: I have a DB I use for training records at work. The training records the employees are trained on are called Standard Operating Procedures (SOPs). The SOPs are placed in categories. There are 4 departments that perform SOP training. The table I enter the SOPs into is called tblSOPs. It consists of the following fields: Field 1- SOPID (a unique number for each SOP - the number cannot be duplicated) Field 2- SOPTitle Field 3- Category (Can be one or up to four categories per SOP - For example: Department A may need to be trained on SOP 123. Department A, B, & C may need to be trained on SOP 234. etc.) I also have a table for categories. (tblCategory) Dept. A has 7 categories Dept. B has 2 categories Dept. C has 7 categories Dept. D has 8 categories This table consists of the following fields: Category DeptID Someone told me, if a SOP (for example) has 4 categories assigned to it, (using my current table structure), enter it 4 times (4 different records) (with different values in the SOPID field), with the same SOPTitle and different categories in each record. I cannot do that because the SOPID cannot be duplicated. I'm confused as to what to do now. MY SOPID has to remain unique. How shoud I change my table structure so I can enter up to 4 categories per SOP? Do you need to see all of my tables in the DB and how they relate to one another to resolve this issue? I really need help with this. If someone has any suggestions, I would really appreciate some help. Thank you, Karen
|
|
#5
November 16th, 2005, 10:04 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Table Design Question
Why, oh why, can't I edit my posts when they go bad????
Anyway, what I tried to put in before . . . tblSOP SOPID SOPTitle tblDepartment DepartmentID DepartmentName tblCategory CategoryID SOPID DepartmentID The tblCategory is where you relate the SOPs to the Departments. Once you have linked the SOPID's and the DepartmentID's, you can populate the table with all the ways that those two are related. You will list all of your SOPs in tblSOP, and all of your Departments in tblDepartment. Each employee is linked to a Department, which is linked to various Categories, which is now also linked to pertinent SOPs. You no longer need tblDepartmentCategoryJoin. Your tblTrainingEvents will look like this: tblTrainingEvents TrainingID EmployeeID CategoryID TrainingDate The CategoryID is what links back to the SOPID.
|
|
#6
November 16th, 2005, 10:19 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Table Design Question
"Karen" wrote: Below are my tables: tblEmployees EmployeeID (one to many with EmployeeID field in tblTrainingEvents) FirstName LastName StartDate EndDate DepartmentID tblDepartment DeptID (one to many with DepartmentID field in tblEmployees) and (one to many with DeptID field in tblDepartmentCategoryJoin) DepartmentName tblSOPs SOPID (PROBLEM TABLE) SOPTitle Category tblCategory Category (one to many with Category field in tblSOPs) and (one to many with CategoryID field in tblDepartmentCategoryJoin DepartmentID tblDepartmentCategoryJoin JoinID DeptID CategoryID tblTrainingEvents TrainingID EmployeeID SOPID TrainingDate I don't know how to structure the tblSOPs table. SOPs can have one or up to 4 categories and the SOPID has to remain unique. HELP! "KARL DEWEY" wrote: I do not see how. I am assuming your Category is something like Safety, Finance, Security, EEO, etc. "Karen" wrote: Can't I just modify the tblCategory? Which consists of Category and DepartmentID field? Thank you "KARL DEWEY" wrote: You will need a new table that has SOPID and Category. Use the two fields to create a compound key. "Karen" wrote: I have a DB I use for training records at work. The training records the employees are trained on are called Standard Operating Procedures (SOPs). The SOPs are placed in categories. There are 4 departments that perform SOP training. The table I enter the SOPs into is called tblSOPs. It consists of the following fields: Field 1- SOPID (a unique number for each SOP - the number cannot be duplicated) Field 2- SOPTitle Field 3- Category (Can be one or up to four categories per SOP - For example: Department A may need to be trained on SOP 123. Department A, B, & C may need to be trained on SOP 234. etc.) I also have a table for categories. (tblCategory) Dept. A has 7 categories Dept. B has 2 categories Dept. C has 7 categories Dept. D has 8 categories This table consists of the following fields: Category DeptID Someone told me, if a SOP (for example) has 4 categories assigned to it, (using my current table structure), enter it 4 times (4 different records) (with different values in the SOPID field), with the same SOPTitle and different categories in each record. I cannot do that because the SOPID cannot be duplicated. I'm confused as to what to do now. MY SOPID has to remain unique. How shoud I change my table structure so I can enter up to 4 categories per SOP? Do you need to see all of my tables in the DB and how they relate to one another to resolve this issue? I really need help with this. If someone has any suggestions, I would really appreciate some help. Thank you, Karen
|
|
#7
November 16th, 2005, 10:50 PM
posted to microsoft.public.access.tablesdbdesign
|
|||
|
|||
|
Table Design Question
Below are my tables:
tblEmployees EmployeeID (one to many with EmployeeID field in tblTrainingEvents) FirstName LastName StartDate EndDate DepartmentID tblDepartment DeptID (one to many with DepartmentID field in tblEmployees) and (one to many with DeptID field in tblDepartmentCategoryJoin) DepartmentName tblSOPs SOPID (PROBLEM TABLE) SOPTitle Category tblCategory Category (one to many with Category field in tblSOPs) and (one to many with CategoryID field in tblDepartmentCategoryJoin DepartmentID tblDepartmentCategoryJoin JoinID DeptID CategoryID tblTrainingEvents TrainingID EmployeeID SOPID TrainingDate I don't know how to structure the tblSOPs table. SOPs can have one or up to 4 categories and the SOPID has to remain unique. HELP! "KARL DEWEY" wrote: I do not see how. I am assuming your Category is something like Safety, Finance, Security, EEO, etc. "Karen" wrote: Can't I just modify the tblCategory? Which consists of Category and DepartmentID field? Thank you "KARL DEWEY" wrote: You will need a new table that has SOPID and Category. Use the two fields to create a compound key. "Karen" wrote: I have a DB I use for training records at work. The training records the employees are trained on are called Standard Operating Procedures (SOPs). The SOPs are placed in categories. There are 4 departments that perform SOP training. The table I enter the SOPs into is called tblSOPs. It consists of the following fields: Field 1- SOPID (a unique number for each SOP - the number cannot be duplicated) Field 2- SOPTitle Field 3- Category (Can be one or up to four categories per SOP - For example: Department A may need to be trained on SOP 123. Department A, B, & C may need to be trained on SOP 234. etc.) I also have a table for categories. (tblCategory) Dept. A has 7 categories Dept. B has 2 categories Dept. C has 7 categories Dept. D has 8 categories This table consists of the following fields: Category DeptID Someone told me, if a SOP (for example) has 4 categories assigned to it, (using my current table structure), enter it 4 times (4 different records) (with different values in the SOPID field), with the same SOPTitle and different categories in each record. I cannot do that because the SOPID cannot be duplicated. I'm confused as to what to do now. MY SOPID has to remain unique. How shoud I change my table structure so I can enter up to 4 categories per SOP? Do you need to see all of my tables in the DB and how they relate to one another to resolve this issue? I really need help with this. If someone has any suggestions, I would really appreciate some help. Thank you, Karen
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Help again from Ken Snell (Query) | Randy | Running & Setting Up Queries | 22 | August 29th, 2005 08:15 PM |
| Table design question | CS | New Users | 6 | June 1st, 2005 06:50 AM |
| Access combo box-show name, not ID, in table? | write on | New Users | 30 | April 30th, 2005 09:11 PM |
| Table and Relationship design problem | douglas jones | Database Design | 2 | March 16th, 2005 11:45 PM |
| Table design question - advice needed | David | Database Design | 3 | June 8th, 2004 02:21 AM |
Linear Mode
