Appointments System

[pauline_redrainbow]

I am using Access 2003 to create an appointments system which holds
appointments at 15 minute intervals, i.e 09:00, 09:15 etc. The system
has two tables, one table has Times, and formatted to time, this field
is the primary key, the second table is the booking table which has
three fields, Date, Time, Client. Date and Time are primary keys. The
time field in the time table is joined to the time field in the booking
form, which creates a one to many relationship. I have two queries,
one query is based on the bookings table, and shows the bookings that
have been made, and the second query has the table Times and the
bookings query, and are linked with an inner join from left to right.
This shows which time slots are available on a particular day. The
booking form is based on the booking table, but I have changed the Time
field into a combo box, and is pointed in the direction of the query
with the available times. If I save and refresh each booking I find
this works well, with the booked times disappearing from the available
list.

The problem I have is this. I want to introduce a user table, like in a
hairdressers, where more than one stylist can access their time slots
to make a booking. I have tried creating a list of staff in a separate
table and having a link table to bring the date and time together for
each member of staff, and make both of these fields (Staff, Time)
primary keys. The booking table will not allow me to create a link
between the fields in this table and the time and staff field in the
booking table. It says it is "inderminate" and won't allow me to
enforce referential integrity. I have also tried creating the whole
table using auto number for primary keys in each table and I find this
brings up lots of error messages.

Could anyway give me any information on how to extend the system for
more than one user or point me in the direction of a website or a book.
I don't have much experience in reading and writing code - I tend
to make things work with command buttons and form operations.

Any help would be appreciated......Ann

[ManningFan]

Is this ABSOLUTELY necessary? I ask because if you impliment MSOutlook
it comes with calendar functionality that will do something similar to
this. No need to reinvent the wheel, so to speak. There are also very
effective calendars (HalfMoon comes to mind) which can probably do this
as well.

If it absolutely, positively needs to be in Access, try adding a
"StaffID" field in your Date/Time/Client table. See if you can string
along the ID, and if so then just create another table with StaffID and
StaffName and link it to the ID field.
pa****************@hotmail.com wrote:

I am using Access 2003 to create an appointments system which holds
appointments at 15 minute intervals, i.e 09:00, 09:15 etc. The system
has two tables, one table has Times, and formatted to time, this field
is the primary key, the second table is the booking table which has
three fields, Date, Time, Client. Date and Time are primary keys. The
time field in the time table is joined to the time field in the booking
form, which creates a one to many relationship. I have two queries,
one query is based on the bookings table, and shows the bookings that
have been made, and the second query has the table Times and the
bookings query, and are linked with an inner join from left to right.
This shows which time slots are available on a particular day. The
booking form is based on the booking table, but I have changed the Time
field into a combo box, and is pointed in the direction of the query
with the available times. If I save and refresh each booking I find
this works well, with the booked times disappearing from the available
list.

The problem I have is this. I want to introduce a user table, like in a
hairdressers, where more than one stylist can access their time slots
to make a booking. I have tried creating a list of staff in a separate
table and having a link table to bring the date and time together for
each member of staff, and make both of these fields (Staff, Time)
primary keys. The booking table will not allow me to create a link
between the fields in this table and the time and staff field in the
booking table. It says it is "inderminate" and won't allow me to
enforce referential integrity. I have also tried creating the whole
table using auto number for primary keys in each table and I find this
brings up lots of error messages.

Could anyway give me any information on how to extend the system for
more than one user or point me in the direction of a website or a book.
I don't have much experience in reading and writing code - I tend
to make things work with command buttons and form operations.

Any help would be appreciated......Ann