Search code examples
databasems-accesscriteria

Value not in query result

OK, third time attempting to write this. Essentially I need to make a query that can check through a table and insure that a value in each record does NOT equal a value in a record from a query

Overview

Query 1 searches through Bookings where: Start Date < ['End Date'] AND End Date > ['Start Date'] displaying the BID (Booking ID) and VID (vehicle ID)

Query 2 searches through Bookings again but removes all records that have the same VID as the results in query 1

the problem is occurring where in query 2, if more that 1 result is displayed in Query 1, then it just displays the whole bookings table.

I currently use:

Field:    VID
Table:    Bookings
Criteria: <>[Query1].[VID]

i have been trying to figure this method out for the last 2 months now and still haven't got any further. any method i try of completing the task only works in certain cases.

essentially i am trying to create a query to see if a vehicle is available for rental for a specific date range.

Any help is really appreciated.

Thank you in advance

Solution

  • It would be useful to see your tables. I assume that your Bookings table shows when vehicles have been booked for rental? So do you have a separate table of Vehicles? If so, then to see what vehicles are available for rental for a given date range, what you really need is to select entries from the Vehicles table where the VID is not in [Query 1]. This would be written as:

    select * from Vehicles where VID Not In (select VID from Query1)