Searching for records that do NOT meet a defined date range

[SmokeEater]

I have a db used to track our company pool car inventory, including check-out/check-in information. Vehicles can be reserved for future use “multiple” times as longs as the vehicle is checked back “in” before the next check-out date. Example:

Car 1:
Reservation #1: Checkout 11/22/11, Return Date 11/26/11
Reservation #2: Checkout 11/29/11, Return Date 12/4/11
Reservation #3: Checkout 12/5/11, Return Date 12/12/11
Etc.

Currently we have 12 vehicles in the pool, and some have may have no current reservations tied to them. What I am trying to do is set up a search form whereby the person responsible for issuing the vehicles can enter 2 dates (check-out and check-in) and get a list of all vehicles that are available during that period of time. All of my previous seach forms are based on looking for data that falls between two dates, however I need to be able to set a system were any reservations that fall anytime during the search (check-out and check-in) dates would be EXcluded, not Included. This would also include vehicles with no current reservation dates.

I have set up a regular query with the pool car number (primary key), and [DateCheckedOut] and [DueDate] fields, both “date” fields obviously, but now I am drawing a blank. I have been able to block out the entries where the check-out date does not equal the date entered, same with the due date, but cannot figure out how to do both at the same time, and most importantly, including all of the dates between the two.

As always I appreciate the assistance provided.

[johnhutchison]

The vehicles available are the ones that are not booked. So first make a query to find that ones that are not available, then use a second query to find any vehicles not in the first list.

A vehicle is not available if there is a booking with Checkout before (<=) the End Date of the search period and Return Date >= the First Date of the search period. The first query should just return the Car Pool Numbers of these cars. Save this query as, say, QryBookedCars.

The second query will be: Select tblcars.* from tblcars where tblcars.CarPoolNumber not in (Select qryBookedCars.CarPoolNumber from qryBookedcars)


(I hope I am not making any silly mistakes here..it is late at night here.)

Are cars booked for whole days? So a car cannot go back out on the same day it comes back?

[SmokeEater]

John,

Would this also include records that are prior to the Check-out date? Vehicles can be reserved for 2-3 months "out", and others can be reserved for use tomorrow...so I would need the ability to search on both sides of the current (check-out/check-in) reserve dates. Plus, as mentioned, there will be instances of multiple reservations, so a vehicle could have a pattern of : reserved, available, reservered, reservered, available, reserved, etc. I just want to make sure I somehow pull out those "availabe" periods of time. I understand the other "null" vehicle query, basically looking for a "null" check-out date, correct?

[johnhutchison]

As I see it the date that a booking is made is irrelevant. All that matters is when the booking is for. I am assuming that when a car is booked you say when you want it, and when you will return it, and that ties up the car both of those days and all between. (When you actually return it is another thing perhaps.)
So a car is not available for a particular period if there are any bookings that overlap the period you want. That is what my first query looks for.
A car is available if it is not in the list of cars that are not available. That is what the 2nd query does.
I have not used the "null" vehicle query you mentioned.
There are probably other approaches to this, but I have used something like this for room bookings in the past.

[SmokeEater]

John. It is making sense now, and I have actually developed both queries, though I have not yet had a time to "test" the one with the date, plus tie the two queries together. Thanks again.

[SmokeEater]

John,

Kind of run into a mathmatical situation....I have the qry that shows the vehicles that are booked, and not available, based on the dates entered. That is good. I have a query that shows me all available cars with no bookings, and that is good. But now I need to take the results of the 1st query, and somehow include criteria in the CarPoolNumber for (not equal) related to the results. In essence, I have identified which vehicles are not available, which means ALL of the other ones with existing reservations are available for other bookings during the dates specified. I need to combine the "negative" results (not available) into positive (other reservations, but still availalbe) and add them to the results of query #2, for vehicles with no bookings, but are availabe. Does that make sense? Would I need to some how make a 3rd query that subtracts the returned records in query #1??? Kind of stuck.

Finally, I am combining the present results (though not accurate) using query #1 as a make-table query, and the query #2 is an append query to the "new" table made in query #1. The CarPoolNumber information from that table will then go to my Search Results form - and that process is working great. Its just that I am not able to show all available vehicles NOT related to the results shown in query #1.

Thanks John.

[ruirib]

John's suggestion still applies. Create a copy of your first query, view it in Design mode and change it so that it only returns the CarPoolNumber for the booked cars. Once you do this, view it in SQL View mode. Now get the SQL and surround it with parenthesis. Then add a select similar to the one John posted above:

Select tblcars.* from tblcars where tblcars.CarPoolNumber not in (The Initial SQL from Query1 changed to return just the CarPoolNumber)

If you have another way to select all the available cars other than something like "select tblcars.* from tblcars ", than use that SQL to replace this in the query.

[SmokeEater]

My apologies to John for probably dissecting his response and putting "too much" into it. I think I overkilled what I was trying to do. Now I can take these results and combine them with those vehicles that are available and having no bookings. I have one more question (I think)...I have multiple records for the save car number. How can I combine these to only show (1) instance of each. By the way, I forgot to mention, the query is set for the Car Number as you described, but the results show data for all the fields in the queries that make up this query.

[ruirib]

To avoid multiple copies of the same selected record use:

SELECT DISTINCT tblcars.* ...

Well you can restrict the fields shown by the query by enumerating the specific fields you're interested in. Careful, as too many fields in the select can affect the outcome of the DISTINCT.

[SmokeEater]

Got it! Actually I had the qry listed twice, which resulted in all of the field data being returned. When I looked at it in design view, I could see the problem. I deleted the 1st query, left the filtered (Not In) CarNumber field by itself, and ta-da!!! I will now use this query as a "make-table" query to get the combined results. THANKS Again!

[ruirib]

Glad you sorted it .

[johnhutchison]

I am glad you have sorted it. I still don't understand why you need a separate query for those that have no bookings. They should be returned automatically by the second "not in" query.

If you really need to combine the results of two queries into one list you can do with a union query.

Select qryone.* from qryone
union
Select qrytwo.* from qrytwo

for this to work the two queries need to have the same fields.