Page 1 of 3 123 LastLast
Results 1 to 15 of 43
  1. #1
    Lounger
    Join Date
    Aug 2009
    Location
    California
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am in the middle of creating my first serious database application as an intern and i'm running into a few problems. If anyone could help I'd really appreciate it.

    I have three seperate tables; An (Employee Table), a (Training Table), and a (Training_Code Table). I have My (Training_Code) table acting as a bridge between my (Employee) and (Training) tables to prevent a many-to-many relationship by having a text data type (Training_Path) primary key on the table link to an identical identifier on my employee table. I have also created a one-to-many with the long integer value (Training_Code) on both my (Training_Code) and (Training) tables.

    I need to find a way to associate the employees with the training courses they are required to take. These requirements are determined by their position in the company and reflected in their (Training_Path) designation. The snag that i have run into is that the Query wizard does not seem to want to allow me to associate a (Training_Path) with multiple (Training_Code) values.

    The goal is to be able to pull up an individual person's training record, be able to see how much they have completed and how much they still need to accomplish. I need to be able to present this data in a graphical format, add and delete records, and produce reports for the managers. I've also been tasked with finding a way for people to sign in when they attend a training and have the program automatically note the time and date they completed the training while adjusting the relevant graphs and tables.

    I'd appreciate any help i can get, i'm stumped!

    {Edit - Using Access 2007}

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    It would be helpful if you sent a compacted zipped copy of your database so we can see the tables and get a better idea of what you are doing.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Welcome to the Lounge!

    In my opinion, this is a rather daunting project for a first serious database application! You are faced with a multi-dimensional problem that will require several many-to-many relationships.

    For example, in addition to the tables that you mention, you will need a table that lists the positions in the company, and this will have a many-to-many relationship with the training table: the position-training table will describe the training courses that are required for a position.

    If you only want to keep track of the current position of an employee, you can add a position field to the employee table (linking to the position table). If you want to keep a history of positions, you can't use a position field in the employee table, you'd need another table that describes the positions an employee has held over the years.

    And then, your training table will describe "generic" training courses. The actual courses are instances of these generic courses, so you need yet another table.

    Before you actually start building the database in Access, you need to analyze the requirements carefully, and think hard about the table structure needed to meet the requirements.

  4. #4
    Lounger
    Join Date
    Aug 2009
    Location
    California
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here's a bare-bones copy, i had to delete identifying information (Security reasons...) but you should be able to get a general idea of what i'm trying to do.

    Thanks for replying!
    Attached Files Attached Files

  5. #5
    Lounger
    Join Date
    Aug 2009
    Location
    California
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Welcome to the Lounge!
    Thanks!

    In my opinion, this is a rather daunting project for a first serious database application! You are faced with a multi-dimensional problem that will require several many-to-many relationships.
    Unfortunately it's my assignment, i'm stuck with it, and i'll have to figure out some sort of solution

    For example, in addition to the tables that you mention, you will need a table that lists the positions in the company, and this will have a many-to-many relationship with the training table: the position-training table will describe the training courses that are required for a position.
    I have included a position column in my employee table and assigned a Training_Path value to each position. I then routed those Training_Paths into a seperate table using a one to many and then from that table to my Training table to acheive an effective many-to-many between my Employee and Training tables.

    If you only want to keep track of the current position of an employee, you can add a position field to the employee table (linking to the position table). If you want to keep a history of positions, you can't use a position field in the employee table, you'd need another table that describes the positions an employee has held over the years.
    Is there a way i can use a one-to-one relationship with a cascading update (but no cascading delete) to capture changes in position? If i delete an employee and then add him back to the employee table won't the autonumber recognize him as a new employee and assign him a new number?

    And then, your training table will describe "generic" training courses. The actual courses are instances of these generic courses, so you need yet another table.
    I hadn't thought of that

    Before you actually start building the database in Access, you need to analyze the requirements carefully, and think hard about the table structure needed to meet the requirements.
    I'm learning this part the hard way. Access is so much harder to work with than excel!

    {Edit - I'm pretty comfortable with excel so if there's a way to do part of the work there and import it into access i'd love to do that}

    -G

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='Padawan15' post='787851' date='07-Aug-2009 11:31']Here's a bare-bones copy, i had to delete identifying information (Security reasons...) but you should be able to get a general idea of what i'm trying to do.

    Thanks for replying![/quote]
    Have you taken note of what Hans has said?

    You really do need to define what you really need and define the tables accordingly.

    Do that first then we can help you.

  7. #7
    Lounger
    Join Date
    Aug 2009
    Location
    California
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='patt' post='787853' date='06-Aug-2009 16:56']Have you taken note of what Hans has said?

    You really do need to define what you really need and define the tables accordingly.

    Do that first then we can help you.[/quote]

    I'll draw out my requirements and post them when i get them digital.

    Thanks for the replies!

    -G

    {Edit - Should have something up by tommorow. I've got to leave my computer. Thanks again!}

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Padawan15' post='787852' date='07-Aug-2009 01:43']Is there a way i can use a one-to-one relationship with a cascading update (but no cascading delete) to capture changes in position? If i delete an employee and then add him back to the employee table won't the autonumber recognize him as a new employee and assign him a new number?[/quote]
    I don't think one-to-one relationships come into this. Use of one-to-one relationships is very rare, and better avoided.

    You should not delete an employee and then add him//her back - it will break the relationships.

  9. #9
    Lounger
    Join Date
    Aug 2009
    Location
    California
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    OK, here goes...

    I have broken down my project into key objectives and Extras:

    Safety Training Database (An access 2007 application)

    Key Objectives of Database:

    - Associate employees with the training courses that they are required to complete.
    - Track courses complete/incomplete
    - Maintain a historical archive of current and past employees and their training records as well as discontinued/modified training courses.

    Add-Ons:

    - Provide graphical representation of my data broken down by unit, team, or individual employee.
    - Provide an interface that will allow employees to sign in for a training through a form and record the time and date of the sign in.
    - Provide access control to prevent users from dismantling database and restrict access of normal employees to other employees records while allowing managers to view the members of their teams etc.
    - The application needs to be as user-friendly as possible to maximize employee acceptance, some of the employees don't have the best computer skills.
    - Administrators need the ability to add and delete trainings and employees, modify dates of training completed or scheduling of trainings, and manually associate extra training courses to individuals or groups after the database is complete.

    -------------------------------------------------------------------------------------------------------

    As of now i have an employee table, a training_path table, and a training table.

    Here are my ideas on the structure:

    Employee Table:
    - Email (Primary key, unique to every employee)
    - Last Name
    - First Name
    - Phone Extension
    - Cell Phone #
    - Home Phone #
    - Unit
    - Team
    - Job Title
    - Training Path

    Unit Table:
    - Contains all possible units and linked to Employee Table via one to many relationship.

    Team Table:
    - Contains all possible Teams and linked to Employee Table via one to many relationship.

    Job Table:
    - Contains all possible Job Titles and linked to Employee Table via one to many relationship.

    Training Path Table:
    - Each Job Title will be assigned a corresponding Training Path
    - Each Training will be associated with a Training Path

    Problem!
    - Each Employee can have multiple Training Paths
    - Each Training Path can be associated with multiple Employees
    - Each Training needs to be associated with multiple Training Paths (For example, Mechanics and Electricians need to participate in the same fire safety training)
    - Training Paths need to include multiple trainings

    Training Table:
    - A unique ID to identify each training
    - Standard under which each training falls
    - Training Name
    - The OSHA mandated interval in which trainings need to happen (new hires only, annual, bi-annual etc.)
    - The company mandated training interval (always either as frequent or more frequent than the OSHA interval)
    - How employees are trained (Medical, outside trainer, internal expert)
    - Alternate training (alternate training methods, usually through various media)
    - When each training is scheduled to happen in the calendar year
    - The alternate date for each training
    - Associated Training Path

    Standard Table:
    - Contains all possible Unique Training Standards, linked to Training Table via one to many relationship.

    OSHA Table:
    - Contains all mandated training intervals, linked to Training Table via one to many relationship.

    Company Interval:
    - Contains company mandated training intervals, linked to Training Table via one to many relationship.

    How Trained Table:
    - Contains all standard Training Methods, linked to Training Table via one to many relationship.

    Alternate Training Table:
    - Contains all alternate Training Methods, linked to Training Table via one to many relationship.

    When Trained Table:
    - Contains the scheduled dates on which each training is projected to occur.

    Problem!
    - Some trainings will take more than one day to complete
    - Sometimes two trainings are scheduled for the same day (especially true for new hires)

    Separate issue - I need a way, preferably through a user-friendly form, to update each employees training record with the date the training was completed. Administrators need to be able to modify this date, but the vast majority of employees will be restricted.

    -------------------------------------------------------

    I'm having issues figuring out how to attack this problem. Any help would be extremely welcome.

    Thanks!

    -G

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You have fields
    - Unit
    - Team
    - Job Title
    - Training Path
    in the Employee table. This would imply that they can be set independently of each other, i.e. the employee's job title has nothing to do with his/her unit or team, and similar for other combinations. Is that correct?

    (PS there is third-party software for keeping track of employee training; in the end it's probably cheaper and more efficient to buy and use that than to reinvent the wheel)

  11. #11
    Lounger
    Join Date
    Aug 2009
    Location
    California
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='787971' date='07-Aug-2009 12:33']You have fields
    - Unit
    - Team
    - Job Title
    - Training Path
    in the Employee table. This would imply that they can be set independently of each other, i.e. the employee's job title has nothing to do with his/her unit or team, and similar for other combinations. Is that correct?

    (PS there is third-party software for keeping track of employee training; in the end it's probably cheaper and more efficient to buy and use that than to reinvent the wheel)[/quote]

    Job title is related to training path, but the others exist completely independently of eachother.

    I'll suggest it, but they're pretty determined to develop this thing in-house on an intern salary because the budget is way tight at the moment.

    -G

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Training path should probably not be a field in the employee table. You can determine an employee's training path from the job title.

    If you want to maintain a history, unit, team and job title should not be in the employee table either, but in separate tables because there can be multiple records for each employee over time.

  13. #13
    Lounger
    Join Date
    Aug 2009
    Location
    California
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='787979' date='07-Aug-2009 13:03']Training path should probably not be a field in the employee table. You can determine an employee's training path from the job title.[/quote]

    That makes alot of sense. I'm changing my path right now...

    If you want to maintain a history, unit, team and job title should not be in the employee table either, but in separate tables because there can be multiple records for each employee over time.
    How would i associate my employees with those three tags if i remove them completely from my employee table?

    -G

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='Padawan15' post='787989' date='07-Aug-2009 22:46']How would i associate my employees with those three tags if i remove them completely from my employee table?[/quote]
    By including the primary key of the employee table as a foreign key in those tables.

    By the way, I would use an AutoNumber field as primary key, not the e-mail field.
    An AutoNumber field takes up only 4 bytes, and it will never change.

  15. #15
    Lounger
    Join Date
    Aug 2009
    Location
    California
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='787992' date='07-Aug-2009 13:55']By including the primary key of the employee table as a foreign key in those tables.

    By the way, I would use an AutoNumber field as primary key, not the e-mail field.
    An AutoNumber field takes up only 4 bytes, and it will never change.[/quote]

    Thanks, I'll do that!

    -G

Page 1 of 3 123 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •