Need serious help with Diary Planner

[neilq5]

Hey guys im new to the forums (and to Access in General) but i wont waste your time blabbering on and i'll just cut to the chase.

I have been hired by a Motorbike training company to design a database as there currently using a paper based system.
I'm gonna need a lot of help with it over the coming months so i'm going to be a regular here.

The first part of the database i have been asked to design is basically taking his paper diary, and transferring it onto a computer.

I have attached the database below (which i got off the forums and added a few fields)

I'll go through everything i need one step at a time hopefully to make it easy and less confusing for all of us.

so here goes

the first part i need help with is on the frmAppointments form.
when booking an appointment, i want to be able to avoid overbooking instuctors (which will become a dropdown menu as soon as i receive the names of each instuctor from the company) and the bikes.
is there a way i can make certain bikes and instructors disappear, based on the times given in the time field above?
so basically, if a certain instructor and a Honda is booked in for 5pm-7pm today, if that same time is chosen in a new appointment, they will disappear, or be shaded out of the drop down until a 'free' time is chosen?

I really appreciate any help i can get, im really going to need it.

Thanks guys

p.s. I have until March 30th to get this part of the database up to scratch.

 

[PNGBill]

How do you store appointment details ?

Have the Data Source for the lookup list to be a query that collects only the available data. Any dates and times that are = do not show in your query result.

 

[neilq5]

How do you store appointment details ?

Have the Data Source for the lookup list to be a query that collects only the available data. Any dates and times that are = do not show in your query result.

Hi PNGBill, Thanks for the reply.
the appointment details are stored like this

Appt
ApptDate
ApptStartTime
ApptEndTime
ApptNotes
ApptLocation
ApptIstruct
ApptBike

Im not great on query's, sorry for being clueless with this but how would i do that query?
is it just simply creating a query, leaving out the time and date fields and saving?

 

[PNGBill]

A query can be on just one table but ussually they ae two or more.

I imagine you would create a query with the tblAppointment, tblInstructor & tblBike.

You would include the dates and times but have the result <> (not = to)

The query would then return dates and times the different instructors are available.
Any prior bookings would not be returned.

Say Instructor John and Bike xyz is booked for 10am Monday. Then 10am Monday would not include John and bike xyz but all other instructors and bikes would be in the list.

Start your query simple and add complexity as you build up. Check the result and where it goes you should then have a good idea what caused the issue.

 

[smig]

I made a schedule form you can use.
I think it will fit your needs
http://www.access-programmers.co.uk/forums/showthread.php?t=187707

I was too busy to update it, but I'll try to find the time for this

 

[neilq5]

Thanks guys, i'll give both options a try and i'll let you know how i get on