Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2009
    Posts
    204

    Unanswered: Using a query to create multiple records

    Hello,

    I've been racking my brain for the past 2 hours on this and can't figure it out. I have two tables, one with job positions and the number of positions, and one with employees and their positions.I currently have a query with a left outer join which returns everything I need EXCEPT for when if there are 5 positions for a job and there are 4 employees filling that job, I want one line to appear without a name.

    The only way I can think of to do this is to have a line in the positions table for each number of that position. I want to avoid doing this, though, to make it easier for the end user.

    Any ideas?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Please post the SQL statement of the query and a short definition of the tables involved.
    Have a nice day!

  3. #3
    Join Date
    Oct 2009
    Posts
    204
    I actually created a workaround which does what I need. However, I'm open to suggestions on how to improve this:

    The tables:
    tbl_Positions
    Fields: Department, Position, PrimaryDepartment, NumberOfPositions, Shift, SortOrder, OperativeWorking

    tbl_EmployeeTable
    Fields: EmployeeName, Primary Department, Shift, JobTitle

    Here is the query I started with:
    Code:
    SELECT tbl_Positions.Department, tbl_Positions.SortOrder, tbl_Positions.NumberOfPositions, tbl_Positions.Position, tbl_Positions.PrimaryDepartment, tbl_EmployeeTable.EmployeeName, tbl_Positions.OperativeWorking
    FROM tbl_Positions LEFT JOIN tbl_EmployeeTable ON (tbl_Positions.PrimaryDepartment = tbl_EmployeeTable.[Primary Department]) AND (tbl_Positions.Position = tbl_EmployeeTable.JobTitle) AND (tbl_Positions.Shift = tbl_EmployeeTable.Shift)
    ORDER BY tbl_Positions.Department, tbl_Positions.SortOrder;
    I then made another query to count:
    Code:
    SELECT qry_OpenPositions.Department, qry_OpenPositions.SortOrder, qry_OpenPositions.NumberOfPositions, qry_OpenPositions.Position, qry_OpenPositions.PrimaryDepartment, qry_OpenPositions.OperativeWorking, Count("1") AS Field
    FROM qry_OpenPositions
    GROUP BY qry_OpenPositions.Department, qry_OpenPositions.SortOrder, qry_OpenPositions.NumberOfPositions, qry_OpenPositions.Position, qry_OpenPositions.PrimaryDepartment, qry_OpenPositions.OperativeWorking;
    Then I made another query to subtract the count from NumberOfPositions:
    Code:
    SELECT qry_CountOfPositions.Department, qry_CountOfPositions.SortOrder, qry_CountOfPositions.NumberOfPositions, qry_CountOfPositions.Position, qry_CountOfPositions.PrimaryDepartment, IIf([NumberOfPositions]-[Field]="1",[NumberOfPositions]-[Field] & " Open Position",[NumberOfPositions]-[Field] & " Open Positions") AS EmployeeName, qry_CountOfPositions.OperativeWorking
    FROM qry_CountOfPositions
    WHERE (((IIf([NumberOfPositions]-[Field]="1",[NumberOfPositions]-[Field] & " Open Position",[NumberOfPositions]-[Field] & " Open Positions"))<>"0 Open Positions"));
    Then I made a union, which gives me the result I wanted:
    Code:
    SELECT qry_OpenPositions.Department, qry_OpenPositions.SortOrder, qry_OpenPositions.NumberOfPositions, qry_OpenPositions.Position, qry_OpenPositions.PrimaryDepartment, qry_OpenPositions.EmployeeName, qry_OpenPositions.OperativeWorking
    FROM qry_OpenPositions
    UNION ALL SELECT qry_OpenPositions2.Department, qry_OpenPositions2.SortOrder, qry_OpenPositions2.NumberOfPositions, qry_OpenPositions2.Position, qry_OpenPositions2.PrimaryDepartment, qry_OpenPositions2.EmployeeName, qry_OpenPositions2.OperativeWorking
    FROM qry_OpenPositions2
    ORDER BY 1,2;
    Any suggestions?

Posting Permissions

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