Results 1 to 3 of 3
  1. #1
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62

    Unmatched Query - modification


    Hi all,

    I have two tables, one is a log table and another is a repository table. I would like to process all rows from the repository table that do not have an entry in the log table AND process anything that has a N is the moved_to_audit field (of the log table).

    I was able to create the unmatched query, which returns everything not in the log table, but I am unsure how to add rows that are in the log table (but have a N flag set for moved_to_audit).

    Code:
    SELECT tracking_repository.id, tracking_repository.client_code, tracking_repository.carrier_code, tracking_repository.mode, tracking_repository.account_number, tracking_repository.bill_type, tracking_repository.invoice_number, tracking_repository.invoice_date, tracking_repository.net_charges, tracking_repository.tracking_number, tracking_repository.sender_company_name, tracking_repository.sender_name, tracking_repository.sender_address_1, tracking_repository.sender_address_2, tracking_repository.sender_city, tracking_repository.sender_state, tracking_repository.sender_postal, tracking_repository.sender_country, tracking_repository.receiver_company_name, tracking_repository.receiver_name, tracking_repository.receiver_address_1, tracking_repository.receiver_address_2, tracking_repository.receiver_city, tracking_repository.receiver_state, tracking_repository.receiver_postal, tracking_repository.receiver_country, tracking_repository.ship_date, tracking_repository.service, tracking_repository.tracking_service_code, tracking_repository.saturday_delivery, tracking_repository.additional_handling_weight, tracking_repository.additional_handling_size, tracking_repository.additional_handling_longest_side, tracking_repository.residential, tracking_repository.zone_code, tracking_repository.package_type, tracking_repository.ship_weight, tracking_repository.bill_weight
    FROM tracking_repository LEFT JOIN tracking_log ON tracking_repository.[id] = tracking_log.[tracking_repository_id]
    WHERE (((tracking_log.tracking_repository_id) Is Null));
    Hopefully I did a good job of explaining that.

    Thanks

  2. #2
    NSearch is offline Advanced Beginner
    Windows 10 Access 2007
    Join Date
    Mar 2019
    Posts
    62
    Can I simply add:

    Code:
    WHERE (((tracking_log.tracking_repository_id) Is Null)) or tracking_log.moved_to_audit = 'N';

  3. #3
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    suggest try it

    this bit

    tracking_log.moved_to_audit = 'N'

    will fail if the field is a boolean in which case it needs to be

    tracking_log.moved_to_audit = False

    or

    tracking_log.moved_to_audit = 0

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

Similar Threads

  1. Unmatched Query
    By Dog17 in forum Queries
    Replies: 3
    Last Post: 08-07-2014, 02:36 PM
  2. Replies: 1
    Last Post: 02-26-2013, 01:45 PM
  3. Query and unmatched records
    By jlgray0127 in forum Forms
    Replies: 1
    Last Post: 03-19-2012, 05:56 PM
  4. Query modification
    By endri81 in forum Queries
    Replies: 28
    Last Post: 02-29-2012, 09:50 AM
  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