Complicated Query (or is it?)

[uksigma]

Hi

I have another problem with my appointment booking database.

I have a table with available times, ranging from 8:00 to 17:00

When booking an appointment you select a time from a drop-down list (combo box), the source is therefore the table of times.

What I want is for the times that have already been taken (entered in the appointments table for that date) to not appear in the list, so that clashes are impossible as only the available times will be shown when booking a new appointment.

I’m sure this is possible with some kind of query but I just can’t work it out. Does anybody know how this would be accomplished? Or is this not possible?

Thanks.

[Scott Price]

Without knowing the names of your field I will have to assume a few things, but this is the general form:

SELECT [FieldName], [FieldName2] FROM [Table] WHERE IsNull([BookedStatusField]) 

This will choose any available time where there is nothing entered in the field you have to record bookings.

Regards,
Scott

[uksigma]

Hi, thanks for replying.

I'm not quite sure that I've got this set up in the way you think, I've probably set it up incorrectly. At the moment the Appointments form has a field that is the start time, this selects times from a separate table that only has one column (times). There is another field called appointment date.

What I was trying to do was somehow check all the appointments on the date selected in the appointment date field and so only display the times in the list from the times table that hadn't already been selected for other appointments.

I don't really understand VB, where would I have to enter that code so that the look up list would only contain the desired (vacant) start times?

Thanks a lot for your help.

[sierra7]

Hi

One solution to this would be to have a small table (lets call it tblSlots) that just listed the times of the appointment slots [SlotTime] (text for each hour, half hour or whatever) and an [ID] field (autonumber). Create this table and add as many slots as there are in a working day; they will number themselves from 1 to whatever.

On the Appointments table (lets call it tblApps) you should hold an integer field for the appointment time (it can look-up what the time is because you dont want to be writing hours and minutes etc)

Lets assume that the time slot field in tblApps is called [AppSlotID] and the date of the appointment is [AppDate]. The control Source for your combo-box would be;

 
SELECT tblSlots.ID, tblSlots.SlotTime FROM tblSlots LEFT JOIN tblApps ON tblSlots.id = tblApps.AppSlotID WHERE (((tblApps.ApID) Is Null) AND ((tblApps.AppDate)=[AppDate]));
 

The Column Count of your combo =2
The Column Widths = 0;2.54 so the first colum is hidden.

After you assign a date to the Appointment you will need to run a Requery on the combo box;-

Private Sub AppDate_AfterUpdate()
Me!cmbSlots.Requery
End Sub

When you then open the combo you should only see listed the 'slots' which are available.

S7

[Scott Price]

It sounds like you would benefit greatly from reading and understanding the concepts in this How-To article: Database Normalization and Table structures.

Please have a look and then examine your database structure to make sure you are complying with the rules called Normal Forms.

Once you have done so, you'll find that the sample query I mentioned earlier should take care of all your needs.

You might enjoy reading a good book on the subject of how to use Access as well. There are several very good ones out there, just make sure that it's at least 2 inches thick (not a joke :-)!

Kind regards,
Scott

[FishVal]

Hi, there.

You may take a look at a similar thread - Iif query.

Regards,
Fish

[uksigma]

Thanks a lot for your help, I've got it working now.
Thanks again, I really appreciate it. :]

[NeoPa]

A minor point, but when checking for Null values in SQL it's more efficient to use the form :

WHERE [X] Is Null

than the function calling form :

WHERE IsNull([X])

IE This is because the "Is Null" construct is within SQL itself and can be included in the optimising. A function call will always mean that each record needs to be processed even if it's later discarded.