Limit number of clients in date range

[bitsbb01]

Ok, not sure if this is even possible or where to even start..

I've got a form that has all the info for a client, eg..
Client First Name
Client Last Name
Client Hours
Client WE/CS/EE
Client Day And Times
Client Phone
Client Comments

Begin Date & End Date (2 boxs) on there for date input..

and what i'm trying to do is limit that any date entered between begin date and end date, it'll check and make sure there is only ever 20 clients on any 1 day..

i thought Datediff would be the way to go, but then again how do you get it to search each day and make sure theres only 20 clients on there..

Any ideas? as this has got me :banghead:

 

[Pat Hartman]

This is a tricky problem. I can offer a curmudgeon solution using several queries in case no one can come up with something more elegant. Create a table that contains a record for each working day. You will have to remember to periodically add new records to this table. I'd put something in the interface that pops up a message when it's time to update the date table. Then create a query that joins to the date table using a non-equi-join. The QBE GUI only supports equi-joins (equal). Your join should be >= BeginDate AND <= EndDate. This query will return as many rows as there are work days between the two dates. The next query takes this query and sums the rows by date with a where clause of >20 for the count of days. The final query takes this result and joins it back to the table looking for rows where the dates with >20 are in the BeginDate-EndDate range to find all the customers for the overbooked days.