Elementary problem eludes me....

[JamesMcS]

Hi all - I've been trying to help stu_c out with his company car database. Basically all it has to do is take two dates and consult the hire table to see what cars aren't on hire between those two dates, and populate the list box with the appropriate car reg numbers. There's a query to find out which cars are on hire around those dates, and the list box's row source is basically a find unmatched records query.

It seems to work when either the start or end hire date matches a date that's already in the hire table, but not when the specified date range is inside an existing hire date. You'll see what I mean from the attached...

I'm missing something glaringly obvious here, but what is it??

 

[JANR]

I don't know if Allen Bowne's clashing of events might help: http://allenbrowne.com/appevent.html

JR

 

[JamesMcS]

Thanks JANR - this seems to compare where existing records clash, ut I'm after a way to evaluate the contents of text boxes against the contents of the table. Have you looked at the DB? It almost looks to me as though the between criteria of the (only) query isn't working as it should...

 

[vbaInet]

Give us an example of a Start and End date and the results you expect to see and why.

 

[JamesMcS]

OK - try 06/03/11 to 08/03/11. I expect DEF456 not to appear (CarID 2) because it's hired out 05/03/11-09/03/11 (HireID 3).

 

[JamesMcS]

I've just realised why it comes up - because hireID 2 says it's out 04/03/11-04/03/11, outside the specified date range.

So, the actual question is - if there's one hit in the unavailable cars query, how do I get it not to show any instance of that car?

 

[vbaInet]

From the top of my head, you should be looking in the set of records that show the most recent hire record, not all the hire records. One record per CarId (or whatever the field is called).

 

[JamesMcS]

Aye - just messing about with the max of hire dates now....

 

[JamesMcS]

Dammit, can't figure this out!

 

[JamesMcS]

Now I'm really confused. Just deleted hireID 2 out, so the only carID 2 record in there has a hire date of 05/03/11 to 09/03/11. The unavailable cars query shoudl therefore pick up hireID 3, carID 2 with a specified date range of 06/03/11-08/03/11, but it doesn't. I'm going mental here!

 

[JamesMcS]

Right. I had the parameters and field names the wrong way round. Pararmeter should be the field, and evaluate that against max of hire start and end:

 

[DCrake]

Go back to basics and when you submit a date range you need to create a record in your table for each date of hire.

Then you can use a cross tab query to display the dates that cars are booked out.

Copy this sql into a new query and view. In this example I have only used the hire start date as the hire dates.

TRANSFORM Count([Car Hires].[HireID]) AS CountOfHireID
SELECT Cars.Car_Reg, People.Person_Surname, Count([Car Hires].[HireID]) AS [Total Of HireID]
FROM People INNER JOIN (Cars INNER JOIN [Car Hires] ON Cars.CarID = [Car Hires].Hire_CarID) ON People.Person_ID = [Car Hires].Hire_PersonID
GROUP BY Cars.Car_Reg, People.Person_Surname
PIVOT Format([Hire_Start],"Short Date");

 

[JamesMcS]

I did think about that approach... I guess that's the only way then. Shame, the DB I just uploaded works ok-ish, except for example when you enter 01/03/11-31/03/11 - it comes up with all the cars as neither date is between any of the hire dates. Annoying!!

 

[gemma-the-husky]

have you sorted this out now

given a target hire of targetfrom to targetto

and an existing hire of hirefrom to hireto

then the car is not available for hire, for any car where a hire returns a true for this

hireto>= targetfrom and hireto <= hireto or
hirefrom>= targetfrom and hirefrom <= hireto

you may need to mess with the <= and >= to get the exact conditions you need - as a car may be returned early enough on a given day, to hire out later that day (similar to hotel rooms occupation)

as over time you will have lots of hires, you can definitely eliminate consideration of any hires where

hireto<targetfrom, or where hirefrom>targetto

depends how far ahead you book them all out.


I think this logic is correct, but it's all aircode. I think to determine whether something is available, you actually need to determine whether it is not available.

 

[JamesMcS]

Yeah that's basically a between statement I think, isn't it? I *almost* have it right - check out the last DB posted. It works absolutely fine except for when you enter a hire from date that's before the first hire from, and a hire to date that's after the last hire to - it comes up as all cars available when it should be none. I was so sure there would be an elegant way of getting this to work...! I suppose a validation rule in the hire from text box saying "You can't enter a date in the past" would probably work OK though....

Thanks for all your input everyone - I've got to do some of my own work now but will have a crack at the list-every-day-hired approach suggested by DCrake.

Good weekend all! Going to be sunny for me tomorrow!

 

[DCrake]

Another approach is to use arrays. Take on the start and end dates and populate an rray with all the interviening dates then do the same for the previous hire dates and then do a comparision.

Don't forget you only need to check dates that are in the future.

 

[gemma-the-husky]

you can't uses max and mins, because of prebooking - and thinking again - my idea isn't quite right either. It only works if all the hires are contiguous.

So - if someone prebooks a car from say 1st June to 8th June, and someone else from June 15th to June 22nd

then (I thought) that to test a hire availability from say 6th June to 12th June - you had to eliminate any vehicles where

a) the 6th of June aleady falls into a hired period, and
b) the 12th of June does the same

however - thinking again - it's more complex than that even - because if there is a hire booked between the dates then this eliminates the vehicle also - eg if there is an existing hire from 10th to 11th June - then this also makes it unavailable.

So maybe you have to test evey day of the hired period individually to see if there is a clash.

Probably DCrake's idea is best - sounds like there is some redundancy, but it may be the most effective solution. Have a daily calendar for every vehicle, and populate the hired days.


Note that there is also the added problem of dealing with time out - time back which I mentioned before. Most car hires are 24 hour's - eg 10am to 10am - so you need to take into account the expected return time, for the required booking. If someone wants a vehcle at 8am, then a vehicle not coming back until 10am may or may not be suitable. In a commercial environment, maybe you could still offer it at a reduced rate


I suspect the whole problem is not so elementary.....