Making a date unavailable

[feeman]

Not sure if this is possible but I am working on a database, that
requires the following function. Be able to book a job in for a given
week of the year, and this then becomes unavailable for anyone else to
book the same week.
I need to be able to do this for multiple people, so they do not get
double booked for jobs.

Anybody got any ideas.

[Kc-Mass]

Re: Making a date unavailable

How about a table with fields of WeekOfYear, Employee, Booked, BookedFor
You would populate the db fields of WeekOfYear and Employee. Also
initialize Booked to False And BookedFor to "". As you book a week for an
employee change Booked to True and fill in Booked For with the
purpose/Client.
You could then query for weeks available for employee X.

In the alternative you could just record the bookings ( all fields) as they
occur and do some dateMath to figure out what is open.


"feeman" <feehally128@fe ehally128.karoo .co.ukwrote in message
news:1162221187 .362087.297400@ h48g2000cwc.goo glegroups.com.. .

Not sure if this is possible but I am working on a database, that
requires the following function. Be able to book a job in for a given
week of the year, and this then becomes unavailable for anyone else to
book the same week.
I need to be able to do this for multiple people, so they do not get
double booked for jobs.
>
Anybody got any ideas.
>

[pietlinden@hotmail.com]

Re: Making a date unavailable


feeman wrote:

Not sure if this is possible but I am working on a database, that
requires the following function. Be able to book a job in for a given
week of the year, and this then becomes unavailable for anyone else to
book the same week.
I need to be able to do this for multiple people, so they do not get
double booked for jobs.
>
Anybody got any ideas.

I would start by reading Albert Kallal's notes on this. He's done a
LOT of the heavy lifting for you. If you are _always_ booking full
days, then this is simple. This works if your rule is:

A person can be assigned at most one job for a single week of the year.

If you have a table
tblBooking(Pers onID, WeekNo, Year)

'--apologies for the Oracle syntax, but you get the idea)
CREATE TABLE Booking(
PersonID INTEGER NOT NULL,
WeekNo INTEGER NOT NULL,
Year INTEGER NOT NULL,
PRIMARY KEY (PersonID, WeekNo, Year),
FOREIGN KEY PersonID REFERENCES People(PersonID )

Then the compound primary key creates a unique index (no duplicates) on
the 3 fields. So you can't insert a duplicate no matter how hard you
try. So once the date is chosen, you can't choose it again. You could
also use comboboxes that get their data from select queries that
eliminate unavailable days. Depends on how your form is set up.