Results 1 to 12 of 12
  1. #1
    BISCUITPUMPER is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    3

    Question Training Database

    I've been given the task to create a training database for a company. I have made a table of employees. Each employee can be in multiple departments and titles which you should be able to select and each department also has many training disciplines you should be able to choose as well. Within each training session should be 4 different types of training with the ability to enter the date in which the employee received such training. I stupidly tried doing this at first using multi-value fields, which didn't work, unsurprisingly. But now I have two data tables titled Department and Titles, with the employees being the primary key and the departments and titles being the other fields as yes/no data. My question is: if I go about with this method, will I have to make a a separate table for each department and all of the training required within those tables, then a separate table for each training session? This seems really tedious and I'm sure there's an easier way to do this.I'm probably just not thinking straight here (too early) and hoping someone could give me a better solution. Much help would be greatly appreciated.

  2. #2
    LillMcGill is offline Dagny fan
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2010
    Location
    Southern USA
    Posts
    70
    I recommend you spend some time reading about database tables, database relationship types, and then normalization. There is an abundance of information free on the internet.

    Making Employee be the PK in a Depts table, considering a seperate table for each dept and training session, depts and title being yes/no fields... these are a few ways I know you haven't done any reading on the subject.

  3. #3
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I think you only need three tables for what you are trying to do:
    1. Employee [EmpID, Dept1, Dept2, Dept3 . . . . other fields],
    2. Department [TrainingType1, TrainingType2, TrainingType3 . . . other fields]
    3. TrainingSession [EmpID, TrainingType1, TrainingType2, TrainingType3, TrainingType4 . . . other fields].

    Employee & TrainingSession would have EmpID fields that can be used to link the two tables.

    Let me know if you have any questions.

  4. #4
    LillMcGill is offline Dagny fan
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2010
    Location
    Southern USA
    Posts
    70
    Every one of those tables is in violation of the very first rule in relational databases. If you are going to do it that way, you lose any benefit of having a database and might as well store your data in Notepad.

  5. #5
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    hear hear, the suggested design by robeen is bad practice

    It should be something more like:

    Code:
    tblEmployees
    EmployeeID (autonumber)
    EmployeeFN (text)
    EmployeeLN (text)
    (other relevant fields)
    
    tblDepartments
    DepartmentID (autonumber)
    DepartmentName (text)
    (other relevant fields)
    
    tblTitles
    TitleID (autonumber)
    TitleName (text)
    (other relevant fields)
    
    tblTrainingArea
    TrainingID (autonumber)
    TrainingName (text)
    (other relevant fields)
    These would be your tables that hold your basic information

    From here you would want tables that would hold combinations of the data in these tables.

    For instance if you wanted a table that had information on which departments a person was a part of you would have this:

    Code:
    tblEmpDepts
    EDID (autonumber)
    EmployeeID (number, foreign key employees table)
    DepartmentID (number, foreign key department table)
    If you wanted a list of classes that were specific to a department you would have this:

    Code:
    tblDeptClasses
    DCID (autonumber)
    DepartmentID (number, foreign key department table)
    TrainingID (number, foreign key training area table)
    and so on

  6. #6
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Yes - My bad. My suggestons do not conform to 1st normal form. Sorry.

  7. #7
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    rpeare,

    I have got into the habit of avoiding AutoNumber because if you delete a record in the table every AutoNumber after the one in the record you delete is changed.

    For instance, in the tblDepartments that you suggest:
    tblDepartments
    DepartmentID (autonumber)
    DepartmentName (text)
    (other relevant fields)
    If we delete the data in the 2nd row in the table - all the other DepartmentID values are changed.
    If DepartmentID is written to another table as a matter of routine [Eg: when an Employee does a training session] - and later the Department ID changes . . . wouldn't that be a problem?

    I've found myself steering clear of AutoNumber for that reason - but if there is a good reason to use AutoNumber, or if the scenario I've outlined misses something basic [like my 1NF faux pas!!] I'd appreciate the information.

    Also - would the tables I suggested that violate 1NF still 'work'? Or would things be totally messed up and dysfunctional?

    Thanks!

  8. #8
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    If we delete the data in the 2nd row in the table - all the other DepartmentID values are changed.
    If DepartmentID is written to another table as a matter of routine [Eg: when an Employee does a training session] - and later the Department ID changes . . . wouldn't that be a problem?
    First, I don't allow users the ability to delete data from my databases through forms. Secondly what you're describing only happens if you delete the LAST record of a table where an autonumber is active then compact and repair the database. When you compact/repair the database the incremental number will go to the next highest value AFTER the largest autonumber in your table. The ID on existing records does not change either on the parent table or on the child table(s). Third an autonumber field is not editable so there is no way for a user to change the ID even if they wanted to, the could however change the description to confuse things but if you have someone who is malicious there's no real way for you to stop them from doing it if they know what they're doing.

    Also - would the tables I suggested that violate 1NF still 'work'? Or would things be totally messed up and dysfunctional?
    It will work, but it is a *lot* more work, depending on the complexity of the information in the database, to code, write queries, reports, data entry forms, etc.

  9. #9
    BISCUITPUMPER is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    3
    Thanks so musch for the replies, everyone. I realized after I started reading up on relational
    hierarchies that I had no idea what I was doing. Now that I understand what I'm doing, I set
    it up like so:

    tblEmployee
    - Employee ID
    | Employee
    |
    | tblDepartments
    ->Employee ID
    - Departments ID
    | Departments
    |
    | tblTraining
    ->Departments ID
    - Training ID
    | Training
    | Finished? (Y/N)
    |
    | tblTrainingType
    ->Training ID
    Training Type
    Date

    And @LillMcGill I set Employees as the primary key because I was given a list with employee
    names only; no info on which department everyone is in, and also the fact that each employee
    can be in multiple departments. But even aside from those two things, I still don't understand
    why it's bad. Can you elaborate, please?

    Also, since I have set up the database in this way, I'm left wondering if it's possible to have
    a set field within a field in the hierarchy. For example, Joe Shmoe is in the Sales and Quality
    departments. There are certain training numbers that are for those departments that I have in a
    master training list that I'd like to incorporate into a field instead of having someone type in
    each individual number. So it would be kind of like this:

    _____[Input]____[Hardcoded from master list]
    Joe -> Sales -> SalesTraining1 | Finished? -> TrainingType1 | Date1
    ___ -> ____ -> _____________________ -> TrainingType2 | Date2
    ___________________________________ -> etc.
    ___________-> SalesTraining2 | Finished?
    ___________-> etc.
    ____-> Quality -> QualityTraining1 | Finished? -> TrainingType1 | Date1
    _______________________________________ -> TrainingType2 | Date2
    _______________________________________ -> etc.
    _____________-> QualityTraining2 | Finished?
    _____________-> etc.

    Is it possible to set something up like this?
    Last edited by BISCUITPUMPER; 08-18-2011 at 07:19 PM. Reason: Tables were messed up

  10. #10
    Access_Blaster is offline User
    Windows XP Access 2010 32bit
    Join Date
    May 2010
    Posts
    339
    Quote Originally Posted by BISCUITPUMPER View Post
    Thanks so musch for the replies, everyone. I realized after I started reading up on relational
    hierarchies that I had no idea what I was doing. Now that I understand what I'm doing, I set
    it up like so:

    tblEmployee
    - Employee ID
    | Employee
    |
    | tblDepartments
    ->Employee ID
    - Departments ID
    | Departments
    |
    | tblTraining
    ->Departments ID
    - Training ID
    | Training
    | Finished? (Y/N)
    |
    | tblTrainingType
    ->Training ID
    Training Type
    Date

    And @LillMcGill I set Employees as the primary key because I was given a list with employee
    names only; no info on which department everyone is in, and also the fact that each employee
    can be in multiple departments. But even aside from those two things, I still don't understand
    why it's bad. Can you elaborate, please?

    Also, since I have set up the database in this way, I'm left wondering if it's possible to have
    a set field within a field in the hierarchy. For example, Joe Shmoe is in the Sales and Quality
    departments. There are certain training numbers that are for those departments that I have in a
    master training list that I'd like to incorporate into a field instead of having someone type in
    each individual number. So it would be kind of like this:

    _____[Input]____[Hardcoded from master list]
    Joe -> Sales -> SalesTraining1 | Finished? -> TrainingType1 | Date1
    ___ -> ____ -> _____________________ -> TrainingType2 | Date2
    ___________________________________ -> etc.
    ___________-> SalesTraining2 | Finished?
    ___________-> etc.
    ____-> Quality -> QualityTraining1 | Finished? -> TrainingType1 | Date1
    _______________________________________ -> TrainingType2 | Date2
    _______________________________________ -> etc.
    _____________-> QualityTraining2 | Finished?
    _____________-> etc.

    Is it possible to set something up like this?
    Don't use Date as a field name it is a reserved word in Access, use TrainingDate or what ever. Also don't use spaces in your field names, its bad practice and leads to extra coding.

  11. #11
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    Do not have your employee ID on your department table. You need a department table that just lists departments.

    For the example you have given you would have these tables

    Code:
    tblEmployees
    EmpID EmpFN ----> other fields
    1     Joe
    2     Jill
    
    tblDepartments
    DeptID DeptName ---> other department related fields
    1      Sales
    2      Quality
    
    tblDeptClasses
    DeptID ClassID ClassName
    1      1       SalesTraining1
    1      2       SalesTraining2
    2      3       QualityTraining1
    2      4       QualityTraining2
    
    tblClassSub
    ClassID SubID SubName
    1       1     Sales1-TrainingType1
    1       2     Sales1-TrainingType2
    2       3     Sales2-TrainingType1
    2       4     Sales2-TrainingType2
    3       5     Quality1-TrainingType1
    3       6     Quality1-TrainingType2
    4       7     Quality2-TrainingType1
    etc...
    From here it depends on when you consider someone to have 'completed' training. Is it on the sub class level? or do they have to complete all the sub classes to be marked as complete. Let's assume for the sake of this example that you want to have a completion date on each sub class (because you can use the list of classes for a department to determine whether they have completed all the classes in their department based on your data entry)

    so you would have these tables which would contain the actual data from your existing spreadsheet.

    Code:
    tblEmpDepts
    EDID EmpID DeptID
    1    1     1 (PK, Joe's employee number, sales department)
    2    1     2 (PK, Joe's Employee number, quality department)
    
    tblEDClasses
    EDCID EDID SubID CompletionDate
    1     1    1     1/1/2011 (PK, tblEmpDepts foreign key, department class, date finished)
    2     2    2     1/15/2011
    Note: don't use special characters in field names (?, #, % etc) other than the underscore (_).

  12. #12
    BISCUITPUMPER is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    Aug 2011
    Posts
    3
    Alright, thanks a bunch. Makes sense why I couldn't use Employee as my PK now.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Creating a Database to track Employee Training
    By osolaru in forum Database Design
    Replies: 9
    Last Post: 08-25-2011, 01:29 PM
  2. Training database for Download
    By macdca in forum Database Design
    Replies: 1
    Last Post: 06-18-2011, 10:28 AM
  3. training video database
    By bkvisler in forum Access
    Replies: 6
    Last Post: 08-24-2010, 09:51 AM
  4. Training Records Database
    By weisssj in forum Database Design
    Replies: 4
    Last Post: 04-21-2010, 03:36 PM
  5. Training Database - Relationships
    By simmurray in forum Database Design
    Replies: 0
    Last Post: 01-12-2007, 03:39 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums