Query to return non matching records

L1882

New member
Local time
Today, 18:34
Joined
Oct 12, 2011
Messages
8
[Solved] Query to return 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?
 
Last edited:
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?

If you do a GOOGLE Search on "SQL Left Join", I belive you will be pointing in the right direction.
 
Hi, Thanks for that. That would seem to give me ones that do match as well but I guess I could then exclude the null values. Many thanks, will give it a go
 
Working a treat thanks.

One final point, some of the entries contain within them CHB B or DWP and I'd like to exclude these as a criteria but I'm struggling to create a criteria that excludes records containing either of these strings - again any ideas?
 

Users who are viewing this thread

Back
Top Bottom