Help needed with a query or solution.

[k3ll1n]

Hey all.

Question if I may. I'm still learning and after having searched and wasted enough time on this, I'm hoping someone here can break it down for me.

Need help with a combobox displaying a query that shows available time slots for that day. It won't show anything that's already been booked and if it can be done, only show items on certain days of the week. ie: Only taking appointments on Tuesday, Wednesday and Thursdays. I have another table for days of the week that I'm currently not using, but could incorporate it as I did the times below perhaps.

(Access 2016)

Here's my breakdown. The most important bits.


tblAppointments

| AppointmentDate | AppointmentTime |


tblTimes

| Show | Times |
|Yes/No |2:00 PM|
|Yes/No |3:00 PM|
|Yes/No |4:00 PM|


frmAppointmentForm

Combobox (cboAppointmentTime)
(currently displaying a list of the records (times) in the tblTimes.Times that have Show (yes/no checkbox) as True via a query)

-----

Now comes the part I'm trying to wrap my head around. Currently when you select an option from the combobox dropdown it enters that value (ie. 2:00 PM) in the AppointmentTime field within the tlbAppointments.AppointmentTime. Good so far.

Though it's showing ALL appointment times, all the time. I need the combobox to display the available times that aren't already a value in the tblAppointments under AppointmentTime for that day only.

In short,

I need the form combobox to display the times listed in one table, minus anything matching that's already been entered in another table, same day.

<> does not = 2:00 PM Or 3:00 PM or 4:00 PM for current Date only, type of thing.

Hope that's somewhat clear. I'm lost looking for the best solution.
Any advice would be appreciated.

 

[CJ_London]

something like

SELECT DISTINCT tblTime.Times
FROM tblTime LEFT JOIN tblAppointments ON tblTime.Times=tblAppointments.AppointmentTime
WHERE (tblAppointments.AppointmentDate<>Date() OR tblAppointments.AppointmentDate is Null) AND tblTime.Show=True

Note Time is a reserved word - don't use it for field, control or table names.

 

[k3ll1n]

Thanks CJ. I understand how most of that query is written. It's been a while since I've used DBs. I'll give that a shot when I get a chance later tonight and report back. Appreciate the quick response.

I remembered that Time was reserved and figured adding an "s" would do.

edit: I see what you mean. my tables are actually tblTimes and Times as the field. Didn't think of it when I was typing it here. Sorry about that. I'll make the adjustments to your query.

 

[k3ll1n]

edit: Seems to be working. Testing

 

[k3ll1n]

CJ. This is what I was looking for. Makes a lot more sense now.

Working well.
Cheers

 

[CJ_London]

happy to help