Results 1 to 2 of 2
  1. #1
    L1882 is offline Novice
    Windows Vista Access 2007
    Join Date
    Apr 2012
    Posts
    1

    Query to exclude non matching records

    Hi All



    I beg your forgiveness but my Access knowledge is limited so this is probably going to be a really obvious thing to most of you but I'm hoping you can help/point me in the right direction.

    I'm using Access 2007 and in my database I have 2 tables. One is a list of 'short ID's' called 'shortids' and each record contains just one field. In another table, called 'loads' I have a lot of individual entries and each one contains a long description field. The first 18 characters of this makes a 'short id'. Now what I'm trying to achieve is a list of the entries where the first 18 characters of the long description in the loads table do not match an entry in the table of short ID's.

    So far I have created a query from the loads table that gives me just the first 18 characters from the long description but I'm now struggling to understand the logic/language that enables me to say 'show me a list of entries from the loads table that do not have a match on the first 18 characters of the long description to an entry in the shortid table.

    Hope that makes sense and can anybody help please?

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    I have two tables - Table5 & Table5_Copy.
    They both had the same data at first - and then I deleted a few records from Table5_Copy.

    This Query:
    Code:
    SELECT Table5.PatientID
    FROM Table5
    Where Not Exists
    (Select PatientID From Table5_Copy Where Table5.PatientID = Table5_Copy.PatientID);
    . . . returns the records that ARE in Table 5 but are NOT in Table5_Copy.

    You might be able to modify this to suit what you need.
    Hope thi shelps!

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

Similar Threads

  1. Replies: 4
    Last Post: 10-25-2011, 10:07 PM
  2. Replies: 1
    Last Post: 10-24-2011, 08:01 AM
  3. Replies: 2
    Last Post: 10-08-2011, 06:33 PM
  4. Replies: 6
    Last Post: 02-10-2011, 07:09 AM
  5. Exclude records within same criteria
    By brooke48 in forum Queries
    Replies: 14
    Last Post: 05-15-2010, 02:15 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