Query NoMatch? (1 Viewer)

[fat controller]

I have a list of vehicles, each of which has a unique identifier (Bonnet Number)

I also have a table where these bonnet numbers can be entered (in text format) if they are in use on that day, and I have a third table which shows vehicles that are currently off the road for whatever reason.

I need a query that will take the list of vehicles, then compare it with the list of vehicles that are on the in use table (Where Date=date specified, and a yes/no field called DeAllocate is null) , and the list of vehicles that are on the off road table, and then show only vehicles that are not showing on either of the other tables.

Is this possible to do in a query, or do I have to do something more?

 

[Jibbadiah]

Query with left outer joins where bonnet number is null.

 

[fat controller]

Ah, now I am about to show just how much of an amateur I am - I don't understand or understand how to do left outer joins?

 

[Jibbadiah]

http://postimg.org/image/me50o6kg9/

 

[Jibbadiah]

View image. Double click the join properties and choose the middle option. i.e. All records from Main table and only those from the other tables where join fields are equal. By doing that and setting the value to null you effectively remove them.

 

[fat controller]

Thank you

And an amazingly quick response too.

The only other thing is how do I get it to take into account that the 'Deallocate' switch is null on the InUse table?

 

[Jibbadiah]

You'll need to write that as a query first and then reference the query instead of the table. At some later stage you can try to investigate sub-queries, but that would be the easiest way for now.

 

[fat controller]

I've just sat eating my dinner and the penny dropped that is what I would have to do, but thank you for replying. My missus wondered what on earth was going on when I had the moment of realisation and muttered expletives at myself

Anyway, I will let you get on with your day - thank you again for your help

 

[fat controller]

Sorry to drag this back up, but I have had to change tack which means that the query also has to change.

Instead of having a record per vehicle per day, I now have a record per day that records all the vehicles and there is a yes/no field for deallocation. So, I have a query which returns only the records where the deallocation tick box is null.

Now, what I need to do is have the all the vehicles in the main table, less those that are in the Unfit table (off road) - this bit is not changed from before; I also need to take out any bonnet numbers that appear anywhere in either of the two 'in use/allocated' queries. (100 fields in each to take into account)

How would I do this?