Results 1 to 6 of 6
  1. #1
    database_1 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    34

    How to auto-populate a row or a better design to do so


    I have a training database. The fields are TRAININGS, which have the different trainings under it (T1, T2...), then I have the name of the employees, BOB, JOHN, BILL (for example). Under the employee names are the dates that they have completed said training that corresponds to the row (Example table below). I have made a form that can add a new training but new employees are being added or can be moved so it is impossible to make one final form to add said training dates for each employee. Usually there is one date that every employee does the training though. Is there a way to input that one date that would populate every row after the column with the training name? Or is there a better way to organize this data where that would be possible? Thank you
    TRAININGS BOB JOHN BILL
    T1 6/12 6/12 6/12
    T2 7/14 7/14 7/14
    T3

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    This is not a normalized data structure. Each employee training should be an individual record in another dependent table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    database_1 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    34
    If I did that, how could I correlate the employee training dates to said employee?

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Your db needs at least 3 tables: Employees, TrainingCourses, EmployeeTraining

    EmployeeTraining
    ID TngID CompDate EmpID
    1 1 6/12/2019 52
    2 1 6/12/2019 18
    3 1 6/12/2019 27
    4 2 7/14/2019 52

    This is a junction table for many-to-many relationship. Basic relational database principle.

    Two ways to handle CompDate. This can be a date field in TrainingCourses and all employees who attended that TngID course completed on the same date. Or can be field in EmployeeTraining to allow different completion dates for same training.

    Build query that includes all 3 tables for output of all related data to a report.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    database_1 is offline Advanced Beginner
    Windows 10 Office 365
    Join Date
    Mar 2020
    Posts
    34
    Thank you so much for your help, I just have one more question. I can make a form to add an Employee Name and an EmpID, same with the trainings. Is it possible to make a form where the user can say which employees have completed said training? Pretty much assigning a TngID to a EmpID without the user going in and doing each manually.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Not sure what you are asking for with statement "without ... doing each manually". Without data entry, how do you expect to create records of completed training? Yes, code can batch create records for all employees for any or all training ID. The more 'user friendly' the more code.

    Options for data entry:

    1. Main form bound to Employees, subform bound to EmployeeTraining with combobox to select training.

    2. Main form bound to Training, subform bound to EmployeeTraining with combobox to select employee

    3. Single form bound to TrainingEmployee with 2 comboboxes to select Employee and Training

    Enter completion date wherever you have this field.

    Causing an entered value to carry forward to next record will require code (macro or VBA). Common approach is to set textbox DefaultValue property in that control's AfterUpdate event.

    VBA for a text field: Me.textboxname.DefaultValue = "'" & Me.textboxname & "'"

    VBA for a date/time field: Me.textboxname.DefaultValue = "#" & Me.textboxname & "#"

    VBA for a number field: Me.textboxname.DefaultValue = Me.textboxname
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. auto populate textbox
    By willfrank in forum Access
    Replies: 1
    Last Post: 04-13-2016, 09:53 AM
  2. Auto Populate
    By jcharbula in forum Forms
    Replies: 3
    Last Post: 07-07-2014, 10:08 AM
  3. Auto populate
    By Rabastan in forum Programming
    Replies: 5
    Last Post: 01-25-2014, 10:35 AM
  4. Auto populate tbl
    By fodzilla in forum Access
    Replies: 3
    Last Post: 06-15-2012, 05:03 PM
  5. Auto Populate
    By co_sportsguy in forum Access
    Replies: 3
    Last Post: 09-01-2010, 01:22 PM

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