Results 1 to 6 of 6
  1. #1
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108

    help with an unmatched query..

    Hello,
    I used the query builder to build an unmatched query and everything worked out good. It was to look on my update table, find all the new employee's and add them to my master table. I need a similar query to update my main table but i need to update the WGI field on my master table of anyone that has a WGI date that is different than what is in the Update table.

    This is the SQL for the first query i made using the wizard:
    SELECT tbl_WIGI_Updates.Emp_ID, tbl_WIGI_Updates.Emp_Name, tbl_WIGI_Updates.Old_Name, tbl_WIGI_Updates.Posn_Desc, tbl_WIGI_Updates.Occ_Series, tbl_WIGI_Updates.WGI_Due_Dt, tbl_WIGI_Updates.Original_WGI_Due_Dt, tbl_WIGI_Updates.Date_LEI, tbl_WIGI_Updates.WGI_Status, tbl_WIGI_Updates.Pay_Plan, tbl_WIGI_Updates.Grade, tbl_WIGI_Updates.Step, tbl_WIGI_Updates.Annual_Rt, tbl_WIGI_Updates.Dept_ID, tbl_WIGI_Updates.Short_Desc, tbl_WIGI_Updates.AO_Code, tbl_WIGI_Updates.WIGI_Approval_Status, tbl_WIGI_Updates.Notes
    FROM tbl_WIGI_Updates LEFT JOIN tbl_All_WIGIs ON tbl_WIGI_Updates.[Emp_ID] = tbl_All_WIGIs.[Emp_ID]


    WHERE (((tbl_All_WIGIs.Emp_ID) Is Null));

    I copied and pasted the code and added the section that is in green txt
    SELECT tbl_WIGI_Updates.Emp_ID, tbl_WIGI_Updates.Emp_Name, tbl_WIGI_Updates.Old_Name, tbl_WIGI_Updates.Posn_Desc, tbl_WIGI_Updates.Occ_Series, tbl_WIGI_Updates.WGI_Due_Dt, tbl_WIGI_Updates.Original_WGI_Due_Dt, tbl_WIGI_Updates.Date_LEI, tbl_WIGI_Updates.WGI_Status, tbl_WIGI_Updates.Pay_Plan, tbl_WIGI_Updates.Grade, tbl_WIGI_Updates.Step, tbl_WIGI_Updates.Annual_Rt, tbl_WIGI_Updates.Dept_ID, tbl_WIGI_Updates.Short_Desc, tbl_WIGI_Updates.AO_Code, tbl_WIGI_Updates.WIGI_Approval_Status, tbl_WIGI_Updates.Notes
    FROM tbl_WIGI_Updates LEFT JOIN tbl_All_WIGIs ON tbl_WIGI_Updates.[Emp_ID] = tbl_All_WIGIs.[Emp_ID] and tbl_WIGI_Updates.[WGI_Due_Dt] <> tbl_All_WIGIs.[WGI_Due_Dt]
    WHERE (((tbl_All_WIGIs.Emp_ID) Is Null));


    I dont really know anything about SQL, could someone please explain what is going on or what i need to do to fix it? The qurey will pull data, it just only pulls the test cases i have used to add as new employees and no other records. Not sure what other info you would need..

  2. #2
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Open the query in design view and add a second left join between the two dates fields and add the second Is Null condition on tbl_All_WIGIs.[WGI_Due_Dt].

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  3. #3
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    Ok, i think i understand.. but not sure.. im still messing around w/ it.. this is what im trying..

    SELECT tbl_WIGI_Updates.Emp_ID, tbl_WIGI_Updates.Emp_Name, tbl_WIGI_Updates.Old_Name, tbl_WIGI_Updates.Posn_Desc, tbl_WIGI_Updates.Occ_Series, tbl_WIGI_Updates.WGI_Due_Dt, tbl_WIGI_Updates.Original_WGI_Due_Dt, tbl_WIGI_Updates.Date_LEI, tbl_WIGI_Updates.WGI_Status, tbl_WIGI_Updates.Pay_Plan, tbl_WIGI_Updates.Grade, tbl_WIGI_Updates.Step, tbl_WIGI_Updates.Annual_Rt, tbl_WIGI_Updates.Dept_ID, tbl_WIGI_Updates.Short_Desc, tbl_WIGI_Updates.AO_Code, tbl_WIGI_Updates.WIGI_Approval_Status, tbl_WIGI_Updates.Notes
    FROM tbl_WIGI_Updates LEFT JOIN tbl_All_WIGIs ON tbl_WIGI_Updates.[Emp_ID] = tbl_All_WIGIs.[Emp_ID]
    FROM tbl_WIGI_Updates LEFT JOIN tbl_All_WIGIs ON tbl_WIGI_Updates.[WGI_Due_Dt] <> tbl_All_WIGIs.[WGI_Due_Dt]
    WHERE (((tbl_All_WIGIs.Emp_ID) Is Null))
    WHERE (((tbl_All_WIGIs.WGI_Due_Dt) Is Null));

    i also tried

    SELECT tbl_WIGI_Updates.Emp_ID, tbl_WIGI_Updates.Emp_Name, tbl_WIGI_Updates.Old_Name, tbl_WIGI_Updates.Posn_Desc, tbl_WIGI_Updates.Occ_Series, tbl_WIGI_Updates.WGI_Due_Dt, tbl_WIGI_Updates.Original_WGI_Due_Dt, tbl_WIGI_Updates.Date_LEI, tbl_WIGI_Updates.WGI_Status, tbl_WIGI_Updates.Pay_Plan, tbl_WIGI_Updates.Grade, tbl_WIGI_Updates.Step, tbl_WIGI_Updates.Annual_Rt, tbl_WIGI_Updates.Dept_ID, tbl_WIGI_Updates.Short_Desc, tbl_WIGI_Updates.AO_Code, tbl_WIGI_Updates.WIGI_Approval_Status, tbl_WIGI_Updates.Notes
    FROM tbl_WIGI_Updates LEFT JOIN tbl_All_WIGIs ON tbl_WIGI_Updates.[Emp_ID] = tbl_All_WIGIs.[Emp_ID] AND FROM tbl_WIGI_Updates LEFT JOIN tbl_All_WIGIs ON tbl_WIGI_Updates.[WGI_Due_Dt] <> tbl_All_WIGIs.[WGI_Due_Dt]
    WHERE (((tbl_All_WIGIs.Emp_ID) Is Null))
    WHERE (((tbl_All_WIGIs.WGI_Due_Dt) Is Null));



  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Did you try to do it in design view as suggested? Click the tbl_WIGI_Updates.[WGI_Due_Dt] and drag it to tbl_All_WIGIs.[WGI_Due_Dt]. You will end up with an equi join between the two. Double click that line and in the Join options select either 2 or 3 to show all records in tbl_WIGI_Updates. Now you have a Left Outer Join. Finally double click the tbl_All_WIGIs.[WGI_Due_Dt] to bring it in the list of columns and put is null in the criteria similar to the Emp_ID.

    In any case the SQL should like something like this:

    SELECT tbl_WIGI_Updates.Emp_ID, tbl_WIGI_Updates.Emp_Name, tbl_WIGI_Updates.Old_Name, tbl_WIGI_Updates.Posn_Desc, tbl_WIGI_Updates.Occ_Series, tbl_WIGI_Updates.WGI_Due_Dt, tbl_WIGI_Updates.Original_WGI_Due_Dt, tbl_WIGI_Updates.Date_LEI, tbl_WIGI_Updates.WGI_Status, tbl_WIGI_Updates.Pay_Plan, tbl_WIGI_Updates.Grade, tbl_WIGI_Updates.Step, tbl_WIGI_Updates.Annual_Rt, tbl_WIGI_Updates.Dept_ID, tbl_WIGI_Updates.Short_Desc, tbl_WIGI_Updates.AO_Code, tbl_WIGI_Updates.WIGI_Approval_Status, tbl_WIGI_Updates.Notes
    FROM tbl_WIGI_Updates LEFT JOIN tbl_All_WIGIs ON tbl_WIGI_Updates.[Emp_ID] = tbl_All_WIGIs.[Emp_ID] AND tbl_All_WIGIs ON tbl_WIGI_Updates.[WGI_Due_Dt] = tbl_All_WIGIs.[WGI_Due_Dt]
    WHERE tbl_All_WIGIs.Emp_ID Is Null AND tbl_All_WIGIs.WGI_Due_Dt Is Null;

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    There are at least two errors in that code
    First of all you cannot have two FROM or WHERE conditions
    Secondly you can't have ... AND FROM ...

    As a starting point, try replacing the second WHERE with AND as well as the second FROM
    Also replace AND FROM with AND.


    There may be other issues to solve after that.
    Did you try using the unmatched query wizard?
    Last edited by isladogs; 05-07-2020 at 03:17 PM.
    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

  6. #6
    willmafingerdo is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2016
    Posts
    108
    Ok, now i have a better idea of what you were saying yes i opend the query in design view and switched to SQL and was trying to mess w/ the code. i didnt realize you meant to drag the tables together and all that too ill try adding your code in and let you know. thanks for the help Gicu!!

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

Similar Threads

  1. unmatched query wizard
    By Jen0dorf in forum Access
    Replies: 7
    Last Post: 12-05-2015, 11:58 AM
  2. Unmatched Query
    By Dog17 in forum Queries
    Replies: 3
    Last Post: 08-07-2014, 02:36 PM
  3. Unmatched Query Qizard.
    By deepanadhi in forum Queries
    Replies: 7
    Last Post: 05-15-2012, 03:50 AM
  4. Query and unmatched records
    By jlgray0127 in forum Forms
    Replies: 1
    Last Post: 03-19-2012, 05:56 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