Results 1 to 7 of 7
  1. #1
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121

    Find Unmatched Query help needed

    I have three tables with the following information. I need an unmatched query to tell me that Employee 1 is missing a timesheet for period 11, subperiod 4; and that Employee 2 is missing a timesheet for period 11, subperiod 3. I have tried joining employee from HRIS to Employee in Timesheets and Pay Date in HR to Pay Date in Time Periods bur cannot get these answers regardless of the types of joins I try. Looks like it should be easy. Database attached.




    Timesheets HR Time Periods
    Employee Timesheet Date FY Year Period SubPeriod Employee Pay Date FY Year Period SubPeriod Pay Date Period To
    1 8/7/2020 2020A 11 1 1 9/4/2020 2020A 11 1 8/21/2020 8/7/2020
    1 8/14/2020 2020A 11 2 2 9/4/2020 2020A 11 2 8/21/2020 8/14/2020
    1 8/21/2020 2020A 11 3 1 8/21/2020 2020A 11 3 9/4/2020 8/21/2020
    2 8/7/2020 2020A 11 1 2 8/21/2020 2020A 11 4 9/4/2020 8/28/2020
    2 8/14/2020 2020A 11 2
    2 8/28/2020 2 020A 11 4
    Attached Files Attached Files

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Have you tried using the unmatched query wizard?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121
    yes, that does not provide the answers I expect based on the tables/data

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    To me, your table structures are not normalized and the relationships are incorrect.
    Consider this structure...
    Attached Files Attached Files

  5. #5
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121
    Thanks Steve, My HR file changes every pay period,so each person has one record per pay date. Each pay date covers a two timesheet periods, so there are two timesheets to each pay date. Anyway, in the dbase you sent how do I query to find unmatched timesheets (which was my original question), ie. employee 1 is missing a time sheet for period/subperiod 11/4 and employee 2 is missing a timesheet for period/subperiod 11/3.

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I need an unmatched query to tell me that Employee 1 is missing a timesheet for period 11, subperiod 4; and that Employee 2 is missing a timesheet for period 11, subperiod 3
    copy and past this to a new query
    Code:
    SELECT H.*, *
    FROM 
    (SELECT HR.Employee, [Time Periods].*
        FROM HR INNER JOIN [Time Periods] ON HR.[Pay Date] = [Time Periods].[Pay Date])  AS H 
    LEFT JOIN Timesheets 
        ON (H.SubPeriod = Timesheets.SubPeriod) AND (H.Period = Timesheets.Period) AND (H.[FY Year] = Timesheets.[FY Year]) AND (H.Employee = Timesheets.Employee)
    WHERE (((Timesheets.ID) Is Null))

  7. #7
    smg is offline Competent Performer
    Windows XP Access 2010 64bit
    Join Date
    Apr 2019
    Posts
    121
    That worked! Thanks Ajax.

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

Similar Threads

  1. Replies: 6
    Last Post: 03-06-2019, 12:01 PM
  2. Question on Find Unmatched Query Wizard
    By vickster3659 in forum Queries
    Replies: 7
    Last Post: 06-17-2015, 09:50 AM
  3. Replies: 3
    Last Post: 09-22-2014, 01:53 PM
  4. Unmatched Query to find Changed info between tables
    By michael.p.ryan1 in forum Access
    Replies: 3
    Last Post: 08-24-2012, 02:22 PM
  5. Replies: 1
    Last Post: 12-08-2011, 01:52 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