A challenge to say the least

[Jayce]

Here is a challenge for the best of you!

I have a cattery system which will be using the following tables:

tblCAT tblVisit and so on

I need to be able to check booking availibility. NOTE: THIS IS NOT THE SAME AS A HIRE-SERVICE SYSTEM. I.E. I need to be able to check the following:

1. For every day of a cat's intended visit...is there less than 40 cats @ the cattery

FOR EXAMPLE:

At the moment I can search to see how many visits will be taking place on ONE particular day. So the challenge is:

HOW CAN I REPEAT THIS QUERY TO CHECK HOW MANY VISITS ARE TAKING PLACE ON EVERY DAY OF A CATS INTENDED VISIT.

*At the moment, my querey can only search for the total number of visits taking place on one day...I need to repeat this as mentioned.


THANKS EVERYONE

 

[Bat17]

Unless someone comes up with something better I would loop through the days in code and flash a message up if there are 40 booked already.

Peter

 

[Jayce]

Just what I thought...

Yeah, that's what I was hoping someone could help me with.

Can anyone therefore tell me how to do this in code:

I have a query that finds out if there are less than 40 cats in on one day. How do I repeat this query for each day that the cat wants to stay - remember, the number of days is variable. The soltuion needs to:

1.count how many visits are taking place on day one.
2. If less than 40, continue to 2b. If not, deny booking. (Pop-up message)
2b. If less than 40, run the query for the next day of the visit.
3. Repeat for each day that a cat intends to stay.

Sorry guys, this is a simple as I can make it!!!! It's a hard one to describe let alone solve. Any ideas or comments are greatly appreciated.

 

[jmurphy]

depending upon how your table is set up, why not just a crosstab query that counts all the cats on each day in the range for the reservation, giving you totals day by day - that way you have a report that tells you which days are problems, so you can offer that to cat owner to see if they can handle the "bad" time period and you can book the balance of the days - jim