none
Access Query RRS feed

  • Question

  • Good Evening,

    I have an access database that tracks each employee assigned to a job each day.  Each job can have up to 20 employees assigned to it each day.  I need to list on a form each of the employees assigned to each job. 

    I have two tables, tbl_employees and tbl_jobs.  The table, tbl_jobs, records each employee assigned to a job via the  employee_id.    Please see below...

    I need to create a query that shows the lastName of each employee assigned to each job. 

    Below was my first attempt. 

    It did not work.  Below is the result.  Obviously it is not what I am looking for.

    Below was my next attempt...

    It did not work either.  Here is the result.  I only get one job returned when I need two returned.

    I made other attempts that are variations of these but they too failed to return each employee assigned to each job.

    Can someone help me understand what I am doing wrong and how to do it correctly, please?

    Wednesday, April 10, 2019 3:01 AM

Answers

  • The setup for your tables is not right. You should have 3 tables, not 2:

    - tblJob (job_id, JobName)

    - tbEmployee (employee_id, firstName, secondName)

    - tblAssignment (job_id, employee_id, AssignmentDate)

    There are 1-to-many relations between tblJob and tblAssignment and between tbEmployee and tblAssignment.


    Groeten, Peter http://access.xps350.com/

    Wednesday, April 10, 2019 6:09 AM
  • By having multiple employee columns in the jobs table the table is not normalized to First Normal Form (1NF), which requires each row in a table to contain only one value of each attribute.  What you have is a many-to-many relationship type between employees and jobs.  This should be modelled by a third table which resolves it into two one-to-one relationship types, e.g.

    tbl_job_employees
    ….job_id  (FK)
    ….employee_id  (FK)
    ….date_assigned

    The primary key of this table is a composite of all three columns.

    For data entry you would use a jobs form, in single form view, and within it a subform, in continuous forms view, based on tbl_job_employees, linked to the parent form on job_id.  The controls in the subform would be a combo box bound to employee_id, but set up to hide the bound column and show the employee names (the control wizard can help you set this up), and a text box bound to the date_assigned column.  The DefaultValue property of the latter can be set to Date() if you wish, to insert the current date by default.

    You might like to take a look at DatabaseBasics.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.

    This little demo file illustrates, amongst other things, how a many-to-many relationship type is modelled, and how this is represented in a form/subform.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Wednesday, April 10, 2019 12:14 PM Typo corrected.
    • Marked as answer by Forgivenbygrace Wednesday, April 10, 2019 11:58 PM
    Wednesday, April 10, 2019 12:13 PM

All replies

  • The setup for your tables is not right. You should have 3 tables, not 2:

    - tblJob (job_id, JobName)

    - tbEmployee (employee_id, firstName, secondName)

    - tblAssignment (job_id, employee_id, AssignmentDate)

    There are 1-to-many relations between tblJob and tblAssignment and between tbEmployee and tblAssignment.


    Groeten, Peter http://access.xps350.com/

    Wednesday, April 10, 2019 6:09 AM
  • Thank you for the advice.  I will certainly try it. 
    Wednesday, April 10, 2019 11:53 AM
  • By having multiple employee columns in the jobs table the table is not normalized to First Normal Form (1NF), which requires each row in a table to contain only one value of each attribute.  What you have is a many-to-many relationship type between employees and jobs.  This should be modelled by a third table which resolves it into two one-to-one relationship types, e.g.

    tbl_job_employees
    ….job_id  (FK)
    ….employee_id  (FK)
    ….date_assigned

    The primary key of this table is a composite of all three columns.

    For data entry you would use a jobs form, in single form view, and within it a subform, in continuous forms view, based on tbl_job_employees, linked to the parent form on job_id.  The controls in the subform would be a combo box bound to employee_id, but set up to hide the bound column and show the employee names (the control wizard can help you set this up), and a text box bound to the date_assigned column.  The DefaultValue property of the latter can be set to Date() if you wish, to insert the current date by default.

    You might like to take a look at DatabaseBasics.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.

    This little demo file illustrates, amongst other things, how a many-to-many relationship type is modelled, and how this is represented in a form/subform.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Wednesday, April 10, 2019 12:14 PM Typo corrected.
    • Marked as answer by Forgivenbygrace Wednesday, April 10, 2019 11:58 PM
    Wednesday, April 10, 2019 12:13 PM
  • Here is another way to get what I think you are looking for using a Crosstab query.


    If this post answered or helped you find the answer to your question, please mark it as such for other Forum users knowledge.


    • Edited by DriveEV Wednesday, April 10, 2019 1:46 PM
    Wednesday, April 10, 2019 1:42 PM
  • Ken,

    Got it, thank you.  I appreciate the guidance.

    Wednesday, April 10, 2019 11:52 PM
  • DriveEv,

    I have never tried a CrossTab Query, but I can see where this may come in handy as I continue working on this project.  Thank You.

    Wednesday, April 10, 2019 11:58 PM