~Please Help~ Cattery Booking System 'Free Pen' Query

[Jayce]

Hi, here is the general schema:

tblCat, tblCustomer, tblPen, tblVisit, tblTreatment, tblCarePackage, tblEmployee

When a customer makes a booking, all I need to do is check that there is less than 20 cats in the cattery for every day of the customer's intended visit range. This isn't the same as a car-hire system where if a car is out for one day during the intended hire, then the booking can't be made - it doesn't matter which pen a cat is put into, as long as there is a pen free for each day of their visit.

i.e. a customer wants to hire a ford escort from 1st-10th january but it is already booked for the 9th onwards - the booking can't be made.

a customer wants to put their cat in ANY pen from the 1st - 10th so it just depends on ONE pen being free for every day of this visit.

Any suggestions? My teachers are hopeless and I need to have implemented my system by 20th January!!! Thanks, Jayce

 

[seth_belgium]

If you want I could take a look at it. Contact me by PM if you're interested and I'll give you my e-mail address. Or if it's no problem for you, you can also post your database on the forum.

Greetz,

Seth

 

[Jayce]

Listing of database

Hi, I haven't got a copy with me at the moment. Basically though, I need a query utilising the following:

VISIT(Visit_Ref, CatID, PenID, Visit_From, Visit_To etc.)

CAT(CatID, CustomerID, etc.)

PEN (PenID, Pen_Type, etc.)

There are 20 pens and the query needs to ask 'is there at least one free pen on every day of a cat's intended visit.

This is simple enough if the cat is staying for one day, but I need to run that query for every day of the intended visit range.

Thanks for your help. Let me know if you need any more info, If you really do need the database, I can whip these tables up for you, thanks.

 

[seth_belgium]

I think I have the answer. Look at the query in attached example. Also look at the dummy-data in the visits-table to try it out.

Seth

 

[Jayce]

Not really what I meant

Sorry, that sort of query just finds any pens which could accomodate the whole of a cat's stay, wheras in reality, that wouldn't be very efficient; consider...

A cat is booked in from 1st Jan - 3rd Jan in pen 1. A customer wants to put their cat in from the 1st to the 10th. The query would return that pen 1 is not available when in reality the pen could accomodate the cat for 7 of the requested days. If any other pen could hold the cat from the 1st - 3rd then the visit would be okay.

Technically, I have made a mistake by listing the tblPEN. What I really need to be able to check is 'is there at least one pen free for every day of the cat's intended visit' (i.e. not giving a cat a specific pen, just making usre the total is under 20)

Thanks for your help though!!!Much appreciated; this really is a big problem that no1 seems to be able to answer

 

[seth_belgium]

Well, just to be clear on 1 thing : You're not gonna be able to do that by using just one query...

I'll think some more about it and maybe it will come to me

Seth