Query that collects data from form

[Learning123]

Hi,

My database is a horse-riding booking database. I have a form of rider details, with a sub form of bookings. I want to create a query that is linked from this form.

The subform has:
Booking ID
Rider ID
Date of lesson
Time of lesson
Horse or Pony ID
Instructor ID
Ride Type
Location
Paid (tick box)

The user needs to know that when they book a lesson with a horse on that date and time, the horse is available (i.e. it is not booked for something else during that time). If the horse is free, then the booking can go ahead. If not, then the user has to choose another horse, or a different time.

Horses also cannot be booked for over 5 hours a day (each booking counts as one hour).

I am thinking:
A query with Horse or Pony ID, Time and Date - the Date and ID are taken from the Form (preferably without the user having to enter anything) - and the lessons the horse is booked for that day are all listed. Then have a sum field (how do i do this?) that adds up how many lessons the horse has. An error message should come up if there are 5 or more lessons booked, saying the horse can't be booked for that day.
Plus there should be a find field that allows the user to type in a time? If the time is not in the list then the booking can be made - if it is then an error message should come up saying that the horse is booked for that time.

Not sure if all of this makes sense? Would be grateful for any advice, or alternative ways of doing this.

Thanks

 

[Tommy888]

Ok well, I'm short on time but I can quickly tell you how to sum up the amount of lessons in a day, use a query that looks at the lessons for that day and use some SQL like

1st Query (rough SQL)
Select Horse_ID Where 'Date of Lesson' = [Input Date]

2nd query, querying the first query.
Count(qry_Lessons.[Lesson no.]) AS [Number of Lessons]

Rought idea, im learning too so if anyone can point out that I am wrong please correct me. The code isn't solid but if you are like me if your actually on access its alot easier than typing on a forum.

 

[Learning123]

thanks for your help but i really don't know how to do that..i'm not much of a wiz at access