Query to exclude non matching records

[L1882]

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?

[Robeen]

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:

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!