Access 97 - pulling up available time slots based on the day of the week

Then let's go back to your table structure
It the"Availability" you list above etched in stone, or is this something that may change every week?

In a general sense, I see something like this:

TblApptSlots
SlotID (PK)
SlotDay
SlotStartTime
SlotEndTime


tblAppointments
AppointmentID (PK)
SlotID (FK)
AppptLocation
...etc.


Then you could do a query with a left join to only show available "Slots"

The schedule is set in stone, unless there is a holiday.

And, I need it to show all appointments, scheduled or open.  

I know what the (PK) means, but what does the (FK) mean?

There are other unknowns here as well...

How far in advance do you need to see the Available slots

Why not simply schedule meetings in 15 minute intervals and just filter out the days/times you don't want to be available. (ex.: If Sunday between 4:45 PM and 11:59 PM, then do not allow) This could possibly save us from creating the "Slots" table

...etc
Database75.mdb

Is this a scheduling system for people several people each of whom have different office hours and who may be forecast to be absent for events like conferences, vacations, training etc?

Ray,

I'm a bit hazy as well, so I think my post may be way off base...

Let's see what the OP replies with...

Enjoy the rest of the weekend...

;-)

Jeff

GrayL:  No, the appointments are for any generic employee who happens to be fulfilling this job for the day.  There is only one employee on a rotational basis 7 days a week.

boag2000:  The available times are etched in stone (until someone changes their mind and wants something else).  We only need 7 days showing at a time, but no appointments in the past.  So it would always be today plus 6.

I am so confuzzled and just can't wrap my brain around how to do this.  I am a very basic access person and know little code so trying to solve it with forms, queries, and macros is proving to be difficult.

I created a way of doing it but it's very rudimentary and there is no way to search for a record to find out when their appointment is.  That probably won't work in the long run.  Also it creates a record for each and every possible appointment whether or not it gets filled.  That's going to grow the database quickly in no time.  Might work well later to figure out how many appointments go unfilled.

Anywho, any help is appreciated.

Thank you

The other issue is what happens if an appointment spans 2 or more "slots"
Is that two appointments?

What happens if an appointment needs to be 20 or 25 minutes?
What if an appointment runs over the allotted time?

This is why most scheduling systems do not assign "slots" You simply schedule your appointments in 15 minute increments.

I may be tied up tonight, ...lets see what GRayL posts...

Can you tell us what you are trying to schedule - facilities, people, supervisors?  We still have no idea where you are coming from.  Please describe what you are dealing with and what you are trying to schedule.  In my case, I have a clinic with 6 doctors and about 7200 patients.  All doctors schedules are for 15 minute blocks, any number of which may be used for an appointment.  The doctors all work different times of day, and different days, and all have forecast requirements to be away for vacation, conferences, etc.  

Can you give us a description of your case - along the lines of the above?

GrayL:  Similar to yours, except that in my case I only have 1 "doctor" and an unlimited amount of "patients".  I work at an animal shelter and am scheduling people to come in and drop off their pet.  We are open for limited hours and will never use the same PK info for appointments.  Each animal is assigned its own ControlNbr ahead of time and that PK would be entered into the appointment time.

Thanks.  You have covered Mon, Thu and Sun.  What are the times for the remaining days?  It looks like there are no breaks for things like lunch?

No breaks at all. Monday through Saturday are the same schedule and Sunday is a shorter day.  No appointments on Thursdays.

As a start, the attached mdb has three tables ClinicAppts, DailyAppts and Nums.  Nums contains the numbers 0 to 6 and are used in qryClinicAppts.  DailyAppts contains the times from 09:00:00 to 17:45:00.  qryClinicAppts creates a recordset containing all the datetimes for the next 7 days (including today) according to your requirements, nothing on Thu and Sun starting at noon.  The remaining days all go from 09:00:00 to 17:45:00.  The append query qapClinicAppts append the records from qryClinicAppts to table  ClinicAppts that are not already in the table. Does this get you started?  
Q-27310815.mdb

Wow, thank you!  Sounds like what I need.  Won't have time today but I will take a look at it tomorrow and let you know :-)

Can you show us the structure of the table which contains the field ControlNbr?  I imagine it is the credentials of the owner and the pet(s)?  What do you want to see on the form besides the 7 day schedule - I imagine you on the phone with an owner - you click on a combobox containing all the owners.  When you click on the correct owner, the form populates with all the info you need about the owner and his pet(s).  What is the max number of pets for a given owner?  

I just got done looking this over and I think it's going to be perfect!  Thank you sooo sooo much.  There is no way I would've thought of this on my own.

The rest of the form with the ControlNbr I have all figured out.  They are going to use the existing form they already use and the schedule is going to be a subform of that form and I have created a button that copies and pastes that ControlNbr into the appointment time they click on.

I do have one extra issue that I need help with (relating to this same question)  In the schedule subform I need to have it separated out for each day.  The way I have it currently setup (which is the rudimentary one that they wouldn't be able to do any search for a record on) is set up so that the there are a total of 7 subforms for a weeks worth of records.  I know that I can do the same thing if I just modify the recordset query you sent me.  The only issue is (and I feel silly for asking and not being able to figure it out on my own) is that they way it is set up in the date field is date and time and I don't know what criteria to enter to limit it to just all records for today, or tomorrow, or the next day, etc.  I was using DateAdd("d",1,Date()), DateAdd("d",2,Date()), etc. but it doesn't recognize Date() so I'm stuck.

You could use a crosstab query to display the whole week on just one subform.  However, because they are not updateable, you need a trick to enter the ControlNumber in the correct record in the table ClinicAppts.

Here's the cross-tab:
TRANSFORM First(ControlNbr)
SELECT TimeValue(ApptDT) AS Appt
FROM ClinicAppts WHERE DateValue(ApptDT)>=Date()
GROUP BY TimeValue(ApptDT)
PIVOT DateValue(ApptDT);


If you're satisfied with how that looks, I wil show you the trick.

Remember, as each day passes, you have to rerun query qudClinicAppts to pick up the new day.  The Open event of the form would be a good place.  I don't know how much history you want to keep, but this would be a good place to control that.  

That query works perfectly!  Please show me your trick to make the recordset updateable.  

Thank you so much for your help.

It would be good if you could post the mdb that you have.  Remove any sensitive data, we just need a couple of 'dummy' pets and the remaining info in the table in which you keep ControlNbr - as well as the form you are using.  Just before uploading using File below, do a compact and repair to keep the size to a minimum.

animallover:  Are you able to upload your mdb?

Here is a very tiny bit of the database.  I put both the tables and forms in the same DB and have stripped out much of the macros and code for simplicities sake.

Thank you again for your help
ForReview.mdb

Your file is protected.  I can't get in.  See the Access Msg
ErrorMsg.jpg

EE wont allow me to upload the mdw file.  Can I email a zipped file with it in it to you?  But, I was thinking... I don't think the crosstab will work for me because it assumes that everyday has the same appointment times but they are different on Sundays.

Did you notice that the appointments on Sunday do not start until noon?  I use the SelTop and SelHeight properties of the Screen object to 'capture' the intended appointment times as well as the SelLeft property to capture the dates.  With those values, I create an Update Query which replaces ControlNbr with the actual value which I presume is somewhere on the form.  Can you not make a copy of your ForReview.mdb, open it, and remove the security features using Tools, Security?  You need to be the db administrator to do this.  I was just trying to use to our greatest advantage the work you had already done with the mdb.  

ForReview2.mdb

OK, I was able to open that mdb.  Keeping in mind you are using Access 97 and I'm using 2003, I will create a form in the mdb I created Q-27310815.mdb and uploaded above.  I will import your table tblAnimalRecord into that mdb, display a few credentials about the animal, and in a subform display the schedule.  Once an animal is selected, the top part of the form will populate with a few of the animal's credentials, and the operator will then highlight the appointment, and click the AddAppt button on the top of the form.  The zero will be replaced by the animal's ControlNbr.  When I get it working in Access 2003, I will convert the mdb to Access 97 and upload it here.  You OK with that?

Yes, please.  Again, thank you so much for your help!

Here we go.  When I created the A97 file I was warned that any functionality in A200 or later would be lost.  I do not believe that is the case here.  tblClinicAppts contains the next 7 days of appointments.  Any ControlNbr with a 0 is available for assigning a ControlNbr.  Open the form frmClinicAppts.  In the combo box select a pet by the pet name.  On the click the other three parameters are filled in.  Move the cursor down to the selected appointment time under the selected date.  Position the cursor at the left edge of the date/time box so that the box becomes selected (turns black).  Move the cursor to the command button AddAppt and click.  Presto!  If you decide to change your mind. select the whole date/time (so that it turns black) and click DelAppt.  Does this get you going?
Q-27310815A97.mdb

Note that my short date format is yyyy-mm-dd - hence pre-pending the "20" to the field name to created the datetime from the field name in the cross-tab query when I need to find the correct record in tblClinicAppts.  As an Arizonan (??), you probably use mm-dd-yyyy.  If this causes you a problem, advise as I have a work-around.

Hi GreyL-

I tried the steps above and got several errors.  I recorded them using the screencast and attached it to this message.

Thank you for your work with this animallover-508881.flv

When I quit yesterday, everthing was working fine.  When I got your message this AM, I opened by A2003 mdb and got the same errors.  I spent the last 5 hours chasing dead ends, and finally came up with the suspicion that ADO might be rearing it's ugly head.  I made these two changes to my code, and the problem went away.

In the code for cmdAddAppt, change this line:

Dim db as Database, rs as Recordset

to:

Dim db as DAO.Database, rs as DAO.Recordset

I noticed in your screencast that the subform never seems to populate?  Can you confirm that the format of the PIVOT field names is yy-mm-dd ddd - ie. 11-10-01 Sat  ??

Before you run the form run the query qapClinicAppts.  That will update tblClinicAppts to today.  If this resolves the error message issue, I will upload a corrected version of Q-27310815A97.mdb

 

I ran the query qapClinicAppts and I changed that line of code and now I am getting another error message but I think it has more to do with the fact that the subform never populates.  It is looking for a form called Query.qxtClinicAppts that doesn't exist.  The query qxtClinicAppts works (and yes the field names are in the format above).

Remember the subform sfmClinicAppts is a control and has as a control source the query Query.qxtClinicAppts.  You should have three queries:

qryClinicAppts - a normal select query which provides the recordset for qapClinicAppts
qapClinicAppts - the append query which updates tblClinicAppts
qxtClinicappts - the cross tab query which uses tblClinicAppts

I have make the corrections and uploaded the new mdb below:
Q-27310815A97a.mdb

There still seems to be something missing.  I can see the subform and see that the control source is set as you have stated above but I cannot open it.  It keeps telling me that the subform you refer to "Query.qxtClinicAppts" does not exist or is misspelled.

Am I perhaps missing a plug in or something?

Ahh.  I was able to open it ok in Access 2010 so there must be a feature in it that is missing from Access 97?  Unfortunately I will have to work in Access 97 for the time being until the entire database is upgraded to 2010.

When I open Q-27310815A97a.mdb in A2003, I get a message to convert or to leave it in A97.  I choose the later and on opening I get the warning I may have lost some features that are not available in A97.  So I say leave it in A97 and when the mdb opens, everything works.  What are your References?

But I am trying to open it in A97.  I don't get any messages when opening it except for the error I get when trying to open the form.  Have you been successful in opening it in A97 and getting the subform to populate?

I don't have A97 but I saved the mdb in A97 format.  Open the form in Design view.  Click on the top center of the subform.  Right click and select Properties.  Click again so that the subform sfmClinicAppts is selected.  Click on the Data tab.  The source object should say Query.qxtClinicAppts.  Click on the down arrrow at the right end of the Source Object property.  It should list all the forms, tables and queries in the mdb.  Please tell me what your list says.  

In A97 it only lists the available forms, not any queries or tables.

Ouch, there's the crunch.  Because the cross tab is dynamic, if I were to build a form, every day the form would change.  How many A97 users?  As an alternative, we could build the cross tab with the dates vertical and the appointments times horizontal - across the page - that would be a Date Field plus 36 time fields from the Pivot.
As a result, the columns in the resulting form would not change.  What do you think?

There are dozens of A97 users, but only 2 that will be accessing this form.  I would prefer not to have the times be the column headers.  Couldn't I just have 7 subforms and have each one be for one day?  That way the first one could be today, the next one be today +1, then the next today +2, etc.  Could use DateAdd in the criteria of a query and have the subforms be based off that?  I tried it but I am not sure how to use the DateAdd function in a field whose format is with date and time.

animallover:  Hi there, I'm still working on this but this past weekend was our Thanksgiving and a final race at the yacht club - I am closing in on a solution using a cross tab query as the sub form with the ability to change the field names on the fly as the date changes.  I briefly looked at the idea of six subforms, but it quickly got very messy.  Hopefully I will have something for you in the next day or two.

Ray

Happy Thanksgiving and hope you won your race :-)

I've got the subform as an updatable form, but to be certain, I've reset all my variables and will wait until a new day tomorrow to make sure that it updates and functions properly.  Soon come!

I had a small problem when I opened the mdb today.  I've fixed it and barring anything else, I will upload the mdb here tomorrow (Friday).  

Here it is.  I hope this is along the lines of what you wanted.
Q-27310815.mdb

I don't think it is working correctly..... I choose an animal, select the appointment time that I want (making sure to choose the entire cell) then click the Add Appt button?  When I do that, the screen seems to refresh but nothing happens.  If I only click inside the cell it tells me I have not chosen the entire cell.

Because there are more appointments in some days than rows on the screen, you have to scroll down to get an afternoon appointment.  In this case, after you press the AddAppt button the screen refreshed to the top of the dataset and you may not be able to see your afternoon appointment entry unless you scroll down again.  Was that the case?

I have written the code so that the first time the mdb is opened on a new day, the function InitializeMDB is run.  This adds the new day(s) to the table tblClinicAppts.  On subsequent openings, the system checks that the table is current, and if so, it exits the function.  I assume this is happening?

I was scheduling a morning appointment and yes, the first time I opened it a window popped up saying that it ran the InitializeMDB function.

So tblClinicAppts is current, frmClinicAppts opens, the subform is populated with the dates and appt times, you have selected an AnimalName, you have highlighted a Date/Appt cell on the subform, clicked the command button cmdAddAppt, and nothing happens when you scroll down to where the original Appt cell is visible?

When you scroll up and down the availalble Appt's, is anything filled in?  If there is an appointment filled in, what happens when you click cmdDelAppt?

So tblClinicAppts is current, frmClinicAppts opens, the subform is populated with the dates and appt times, you have selected an AnimalName, you have highlighted a Date/Appt cell on the subform, clicked the command button cmdAddAppt, and nothing happens when you scroll down to where the original Appt cell is visible?  Correct

When you scroll up and down the availalble Appt's, is anything filled in?  If there is an appointment filled in, what happens when you click cmdDelAppt? Nothing is filled in.  I went into the table and entered a record.  When I tried to delete it, nothing happened but an apparent screen refresh.

So where are we at?

animallover:  Have you had a chance to try out the new mdb?

GrayL-

Sorry for the long delay.  My organization decided not to go ahead with the scheduling and decided to stick to walk-ins so I was pulled off the project.

Thank you for all your help.  From what I was briefly able to look at the new database you sent me worked great.