Invert select query

[dlp2k]

[SOLVED] Invert select query

Hi All - I have a question where I need to effectively invert the results of a select query.

I have multiple tables in the database, but the 2 I'm looking at are TBL.Trip and TBL.TripDiary

A trip is logged in the trip table, and then an operator logs a diary entry against the trip. One of the options that the operator selects is when they receive an email back from the traveller, this is logged as a diary entry.
I need the query to look at the DIARY_Action field and select all records in the TBL.Trip that DO NOT CONTAIN an entry for Diary Action "4".

I can produce a select query that gives me the results to see all trips that HAVE had a response, with a simple IS LIKE "4" query in the action field.
If I change this to IS NOT LIKE "4", I get the results that I need, but duplications due to the various other "diary" entries in the table".

I simply need the inverse of the IS LIKE query but cannot see how to remove the duplicates?
Can anyone help at all please? I think its the logic that I'm missing.

 

[Frothingslosh]

What precisely is in Diary action 4 of no entry has been made? Is it NULL or an empty string? If it's null, then you could just make your standard query, but instead of using

Is Not Like "4"

use

Is Null

If it's an empty string, then just use

=""

 

[pbaldy]

If I'm understanding correctly, try two queries. First create one that finds all the 4 entries and their trip IDs. Then use the unmatched query wizard to compare that against all trips.

 

[Frothingslosh]

Or I might have misunderstood you and pbaldy has it right!

 

[dlp2k]

Well guys, thanks very much for your help! It is very much appreciated!

pbaldy had the right answer and it works perfectly, but thanks for the offer of help frothingslosh!

So, for anyone else trying to do something similar:

- Create a normal select query to select the criteria you "DO NOT WANT" to be shown
- Create an unmatched query
- Select the table that contains ALL RECORDS, and the comparison "Select" query that you created
- Use the primary key as the reference
- Results will be display only the records NOT in the initial query

I searched for a solution to this on a few different forums before coming here, so many thanks for the "INVERT SELECT QUERY" solution

 

[pbaldy]

We were happy to help!