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(PersonID, 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.