I am trying to build a database for Training records, these are the tables that I have created..Training, Employee, Position and SOP. I have attached a sample of my training record table. As you can see not every person gets trained on the same SOP's so each employees training record is different so I'm not sure how to query a single employees training record so that I can update them as needed. Thanks in advance for any suggestions.:banghead:
Training Records
- Thread starter ResTech
- Start date
You need to read up on data normalization (http://en.wikipedia.org/wiki/Database_normalization). The biggest glaring error I see is that you are storing actual data in field names (people's names shouldn't be field names).
You definitely need a junction table (http://en.wikipedia.org/wiki/Junction_table), but without seeing your other tables I can't tell you exactly how that should be set up. A junction table defines a many - to - many relationship between tables, which is exactly the relationship you have between employees and training (many people can be trained many SOPnums and many SOPnums can have many people trained in them).
Post your entire structure with sample data and I can help more specifically.
You definitely need a junction table (http://en.wikipedia.org/wiki/Junction_table), but without seeing your other tables I can't tell you exactly how that should be set up. A junction table defines a many - to - many relationship between tables, which is exactly the relationship you have between employees and training (many people can be trained many SOPnums and many SOPnums can have many people trained in them).
Post your entire structure with sample data and I can help more specifically.
Plog, Thanks for the info so far, I am reading up on junction tables. I have attached my other 3 sample tables (they didn't convert very well). I know using names for fields isn't a good idea but I chopped up the excel spreadsheet where our training records are kept and I couldn't come up with a way around this. Now I think I have too much repetition and a big mess whilst I chase my tail. Thanks so much for your input.
It looks like you have more than two moving parts. It seems you have Employees who need to take training courses, but those training courses can have multiple revisions. Do revisions matter? Do you care that someone had SOP001 Revision 4, or do you just care that someone had SOP001?
You need a table that list just your SOPs, then the junction table would link Employees to SOPs it would look like this:
EmployeeTraining
EmployeeID, SOPNum, TrainDate
1, ENG001, 10/5/2011
2, ENG001, 4/5/2013
1, ENG002, 10/6/2011
2, ENG002, 4/5/2013
2, BUS001, 4/5/2011
Depending on your answer to my revision question, it might contain that information, or you might even have another junction table between revisions and SOPs.
Give building this a shot in Access.
You need a table that list just your SOPs, then the junction table would link Employees to SOPs it would look like this:
EmployeeTraining
EmployeeID, SOPNum, TrainDate
1, ENG001, 10/5/2011
2, ENG001, 4/5/2013
1, ENG002, 10/6/2011
2, ENG002, 4/5/2013
2, BUS001, 4/5/2011
Depending on your answer to my revision question, it might contain that information, or you might even have another junction table between revisions and SOPs.
Give building this a shot in Access.
Some people need to train on all SOP's and others need to train on a few select depending on their position. As a revision changes on an SOP I need to send out the new one to anyone that has previously trained on it and then go back and update their training records.
I have attached a training record that gets sent out when a new employee starts.
I have attached a training record that gets sent out when a new employee starts.
Last edited:
If you are suggesting using ID numbers as field names the answer is no. You do not store information relevant to the data in field nor table names.
You need a table that list just SOPs and then a junction table to relate SOPs to employees.
You need a table that list just SOPs and then a junction table to relate SOPs to employees.
- Local time
- Yesterday, 19:50
- Joined
- Jan 23, 2006
- Messages
- 15,574
I have attached a jpg that shows some relationships for Employees and Positions. This was in response to a different post previously. It dealt with Employee Training and the Training required for Positions.
I hope it's useful to you and shows some of the concepts involved.
I hope it's useful to you and shows some of the concepts involved.
Attachments
Plog, I have created the table as you suggested in this post and now I'm having trouble with my query criteria. There is a list of SOP's that people may need to train on, some may need to train on all of them like myself so I did the Like"*", some may need to train on many of them but not all, I have tried pasting all of them in the criteria but I run out of room. Do I need to think of a different way to query them?
Last chance. You gave me a table name and 4 field names. Then you gave me 4 pieces of data; for which field I have no clue. Then I think you posted either 2 more table or field names.
Post starting data and ending data. You want me to build a map between two places? You need to give me where I need to start and where I need to finish.
Post starting data and ending data. You want me to build a map between two places? You need to give me where I need to start and where I need to finish.
SopTbl
SopNum : SopDesc : SopRev : AnnualTrn
BUS003 : Data Ent : 2 : Y
BUS005 : Drawing : 4 : N
CUS001 : Customer : 1 : Y
I am adding to the criteria each SOPNum that each person needs training on so that I can give them a training record but the query will only let me put so many in, maybe easier to have it list the ones that aren't needed.
SopNum : SopDesc : SopRev : AnnualTrn
BUS003 : Data Ent : 2 : Y
BUS005 : Drawing : 4 : N
CUS001 : Customer : 1 : Y
I am adding to the criteria each SOPNum that each person needs training on so that I can give them a training record but the query will only let me put so many in, maybe easier to have it list the ones that aren't needed.
Last edited:
