Assistance need with SQL query (Sit behind a combo button)

[robtyketto]

Scenerio
~~~~~~
A booking system for a sports hall can allow a coach to be hired.
Each coach has his own works rota, he works xx number of days in the week and can work xx hours in the day but is booked in 30min slots (Database allows for flexability).
They are also specialise in teaching individual sports and cannot teach all sports, and charge different amounts per sport per experience.

There is also the issue of if the coach has been double booked.
A booking table stores Data, times, Coach_ID.


Below are the table relationships to cater for the above scenerio :-

Coach availibilty
~~~~~~~~~~~~
Coach_ID (uniquie number for coach)
Day_of_the week (numeric field 0-6 to represent days of the week)
Time (08:00,08:30,09:00,09:30)

Therefore for just 1 day for one this table would contain 20 odd records as 1 row per timeslot.

Coach
~~~~~
Coach_ID
Coach_Firstname
Coach_Secondname

Used to store coach staff personal information (name etc..,)

Coach_Rates
~~~~~~~~~~
Coach_ID
Sport_ID (Numeric value of sport, tying in the sport to a coach)
Coach_Price (Currency)

The booking form is populated with Date, start time,end time & sport before allowing to check if a coach is available for the sport
the customer has choosen to be played.

Therefore the Date & times on the form (combo boxes) require to be used to map onto the date & time in coach availability.

Ive generated the following SQL (doesnt work need some advice!) ~

*Check if coach is available on rota and can place that sport

Select c.Coach_Id, c.coach_first_name, c.coach_second_name
from Coach c, coachrates cr
where c.coach_ID = cr.coach_ID
and cr.sport_ID = me.comboSport.value
and weekday(me.comboDate.value) in
(
Select day
from coach_Availability
and me.comboStarttime.value = time
)

*Allows to check if booking have taken place previously

SELECT c.Coach_Id
FROM coach c INNER JOIN bookings b
ON c.coach_ID = b.coach_ID
WHERE me.combodate = b.date
AND b.starttime Between Me.starttime And Me.endtime


I dont have much experience in vba code, can I try my queries out in the query editor and use parameters replacing the values required in combo boxes?

I need help with logic and syntaxetc..,

 

[robtyketto]

Ok, so after learning alittle more of the syntax of VBA I created this below, assigned it to an event and now get a syntax error

"The expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to variables."

Me.comboCoach.RowSource = "Select c.Coach_Id, c.Coach_first_name, c.Coach_last_name " & _
"FROM Coach c INNER JOIN Coach_rates cr ON c.Coach_ID = cr.Coach_ID " & _
"WHERE cr.Sport_ID = " & Me.comboSport.Value & _
"AND weekday(" & "'Me.DTPicker9.Value'" & ") IN" & _
"( SELECT day_of_the_week " & _
"FROM Coach_Availability " & _
"WHERE Start_time = " & "'Me.Combostart.Value'" & ");"

Can anyone help me out here?
Thanks !!

 

[VicRauch]

Use the Query grid to debug SQL statements before trying to put them into a Row Source property.