Solved

Help with appointment table, recurrences.

Posted on 2006-06-19
76
594 Views
Last Modified: 2016-08-29
I have an appointments table.  In that table it has staffID,Appointment, PatientID, Remarks

I would like to be able to have recurrences.
0
Comment
Question by:candg
  • 37
  • 31
  • 8
76 Comments
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
Is the "appointment" in your table a date field? You need a couple of elements in order to create recurring appointments. First you'll need to know how many occurances you want, then you'll have to decide on the increment..ie once a week, once a month etc. It would probably be easiest if you created a form to allow you or your user to select these items. A quick function to add the data to your table based on those two items and how they apply to the original appointment date is all that is needed after that.
J
0
 

Author Comment

by:candg
Comment Utility
Right what I was thinking is that i'm displaying the schedule of sorts for each staff member.  Then from there I can double click the date/time (appointment) and it will open an add appointment details form of some sort. The patients would come only twice a week for 6 months.
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
Taking into account the odd increments, (twice a week) for 6 months.

I think first off you need a function that will allow you to choose "Days" of the week. Ex: Tue and Thu, then a start date. It will calculate the date for each occurance of those days for the next six month period.

Are you familiar with DateAdd or DateDiff in Access? Are you going to try to take into account Holidays, vacations of your Staff?
J
0
 

Author Comment

by:candg
Comment Utility
Holidays and vacations as well as weekends have all been deleted from a query.  I do however have a function that shows the days of the week but the problem is that it's appointments schedule so it has times in it.  So I forgot to say that.  The patient needs to be able to be booked off for anywhere from 15 mins to 1hr.  Here is what it looks like.
frmappointments StaffID Appt PatientID
CGUT 6/19/2006 8:00:00 AM  123456789
CGUT 6/19/2006 8:15:00 AM  123456789
CGUT 6/19/2006 8:30:00 AM  987654321
CGUT 6/19/2006 8:45:00 AM  987654321
CGUT 6/19/2006 9:00:00 AM  489423654
CGUT 6/19/2006 9:15:00 AM  489423654
CGUT 6/19/2006 9:30:00 AM  489423654
CGUT 6/19/2006 9:45:00 AM  489423654
CGUT 6/19/2006 10:00:00 AM 521456447
CGUT 6/19/2006 10:15:00 AM 521456447
CGUT 6/19/2006 10:30:00 AM (Blank indicates no appointment)
CGUT 6/19/2006 10:45:00 AM (Blank indicates no appointment)
and so on until.....
CGUT 6/19/2006 6:30:00 PM  (Blank indicates no appointment)

0
 

Author Comment

by:candg
Comment Utility
StaffID Appt                           PatientID
CGUT 6/19/2006 8:00:00 AM  123456789
CGUT 6/19/2006 8:15:00 AM  123456789
CGUT 6/19/2006 8:30:00 AM  987654321
CGUT 6/19/2006 8:45:00 AM  987654321
CGUT 6/19/2006 9:00:00 AM  489423654
CGUT 6/19/2006 9:15:00 AM  489423654
CGUT 6/19/2006 9:30:00 AM  489423654
CGUT 6/19/2006 9:45:00 AM  489423654
CGUT 6/19/2006 10:00:00 AM 521456447
CGUT 6/19/2006 10:15:00 AM 521456447
CGUT 6/19/2006 10:30:00 AM (Blank indicates no appointment)
CGUT 6/19/2006 10:45:00 AM (Blank indicates no appointment)
and so on until.....
CGUT 6/19/2006 6:30:00 PM  (Blank indicates no appointment)

sorry should look more like this.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
I believe in the previous question you said there were about 60 staff, your clinic hours were 8:00 to 18:00 weekdays only, with 1 hour off for lunch 12:00 to 13:00, no holidays, 15 minute appointment periods, and a patient could have up to 2 hours (8 periods) in one visit.  To give you an idea of the table size - 60 staff * 250 workdays per year * 36 appt periods per day = 540,000 records.  Have you built this appointment table?  Do you know when you start with a patient how many appointment periods will be required for each visit on the same two days per week?  Does each patient always see the same staff member?

BTW, the previous question is at:

http://www.experts-exchange.com/Databases/MS_Access/Q_21859880.html#16936114
0
 

Author Comment

by:candg
Comment Utility
Appointments table is built.  

I don't understand "Do you know when you start with a patient how many appointment periods will be required for each visit on the same two days per week?" , but I'm guessing here...  The patients will be recurred for 6 months.  So for example, patient   123456789 George Lopez will come Monday and Wednesday every week for 6 months to see:
cgut @ 8:00am - 8:30am
SDAV from 8:30-9:00am.  

"Does each patient always see the same staff member?"
Not necesarrily lets just say that the patients has a default staff memebe.  Here's how it will work.  I got more infor and it's only 1 hour.  Patient sees each staff once

StaffID Specialty Appt                           PatientID
CGUT      OT           6/19/2006 8:00:00 AM  123456789 George Lopez
CGUT      OT          6/19/2006 8:15:00 AM  123456789 George Lopez

StaffID Specialty    Appt                           PatientID
SDAV      ST             6/19/2006 8:30:00 AM  123456789 George Lopez
SDAV      ST             6/19/2006 8:45:00 AM  123456789 George Lopez

Now Notice that this patient saw two staff members (two different specialties) for 30 minutes.  Now...I would like for that to be a recurrence for 6 months.  Once done it might have to change, so if another staff member is in the same specialty as the one the patient sees normally then that patient could be seen by that staff member.  so...
StaffID Specialty Appt                           PatientID
CGUT       OT          6/21/2006 8:00:00 AM  123456789 George Lopez
CGUT       OT         6/21/2006 8:15:00 AM  123456789 George Lopez

StaffID Specialty Appt                           PatientID
SJAG       ST           6/21/2006 8:30:00 AM  123456789 George Lopez
SJAG       ST          6/21/2006 8:45:00 AM  123456789 George Lopez

Notice how SDAV and SJAG (staff members) are the same specialty and are therefore able to see the patient.
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
So say you want to book a patient next Tuesday...you'd want to be able to view all the open appointments  for that day right? then select the open time and duration of the appointment.....then you'd want to do this for each Tuesday until the 6 months is over. OF course as you go out further and further, more and more appointment times would be available and would make it easier, but initially, you'd have to check? You could do "First Available" I suppose but would still need to define that. First Available 30 minute block.

Just outlining here:

Select Tuesday + 7(days)  <gets the appointment date
This appointment will need 45 minutes
Open up the Appointment table and grab all records for that date
Here you'll have to identify the first appointment that has 45 minutes available
Here you need to decide whether you want to change it to another time or not (in case the patient can't make it)
Then grab the next day in the week (Thursday)
do it all again for this week

Then go to the next week in your 6 month period.

So the more information you can provide up front to your function, the better. Also, if you simply just grab and assign the date without any kind of acceptance manually...then you can come back once it's all done and make mods that way.










J
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
GRayL,
You have worked on this previously? I don't want to trod.
J
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
J: Yes.  Check the link back to the previous and the link there back to its previous.  Trod away;-)

Ray
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
This is actually quite involved. I almost recommended using MSOutlook Scheduler and just using the Object method to interact with it.

I looked around some to see if a scheduler solution was already on EE but in the short time I spend, no joy.

Did I read this right? you've created a table with appointment times for the year in it?
If so, then it's a matter of selecting 15 minute blocks for a given Staff Member on a given day and simply writing in the name of the Patient as many times as his appointment duration requires.  Still wondering about verifying the appointment times with the patient. Happens after it's booked?
J
0
 

Author Comment

by:candg
Comment Utility
J that's right on all accounts.
0
 

Author Comment

by:candg
Comment Utility
can u give me more info on scheduler... is it something I can just code in with vba?
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
I re-read what you wrote about specialty and that adds another layer of complication but....

IF I've got the patient's appointment (existing) in front of me...and I want to schedule a new appointment, I doubleclick on the appointment time. A new form opens

This form has a checkbox for Monday, Tuesday, Wednesday, Thursday and Friday.
This form has two unbound text boxes...one for the staff member and one for the specialty
I have a button that says recurring that is disabled until I select at least one day, and a duration.

Not sure how to display or select duration yet. Any rhyme or reason to that? Thinking a dropdown that displays 1 week 2 weeks 3 weeks 4 weeks....etc???

Your form also has an appointment duration field. 15 minutes, 30 minutes, 45 minutes etc...???

Now....choose your specialty because if there aren't any appointment times avail next tuesday for staff member JOE, then find another staff member with a time open then. Thinking Dropdown box listing all specialties???

So now I have this..
Today's date. And I want to start my visits for the first selected weekday AFTER today. If today is Monday and I want to see him on Tuesdays and Thursdays, choose the very next day? or the following Tuesday?
So "Next" in this case can be Next, or Today + 7 THEN Next.

I know I want to see him on Tuesday and Thursday
I know what days I want. I know how long each appointment will be. 45 minutes
I know how long I want to see him. 6 Months
I know what specialty to specify

These will get fed to your Appointment calculator. where they will update the Table with the name of the patient or patient ID for each sequential increment of 15 minutes available for that Staff Member OR specialty on that day.

Are the patient name and patiendID two separate fields?





0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
Do a search here on EE about using outlook scheduler. I found about 6 really good threads when I did it. I hav e to run to a Dr Appt...back in a while.
J
0
 

Author Comment

by:candg
Comment Utility
Here's how I see it.  I have a form that is only showing the appointments for that Staff member.  So first recep chooses staff member which requeries form and shows dates and appointments for that staff member.  I don't know if maybe we could have a start time and end time.  start time 8:00am  end time 8:30am....  Then either a button or form is double clicked.  Which would pull up a form that has the staff member already in there, a dropdown box for patients, a option boxes for mon wed tues thur friday, another option box for recurrence or one time, then a dropdown for 15 min increments up to one hour.  Then maybe a start date and and end date?  And of course is it's reccuring then have the option for 6 months because that's all they are really going to be going up to.   So that way if perhaps the person needs to be seen by another staff member he/she could do so without recurring.   Man this is really tough huh...
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Are we to avoid back-to-back days when scheduling Appointments, ie, Mon/Tue or Tue/Wed, etc? If so, is Fri/Mon ok? Any rules here?
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Is the two days in a week the patient's preference?
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
How many specialties, how many staff, and how many records did you wind up with in the StaffAppts table?
0
 

Author Comment

by:candg
Comment Utility
No rules as far as back to back...just do to the fact that if a patient needs to be rescheduled it can be the next day.  The two days a week are more of availability first then patient preference.  
3 specialties.  Staff members can fluctuate but no more than 100 (I believe right now its 25) .  22532 is the number of records I got, with two dummy staff members.  
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
22536/36 periods per day/25 staff = 24 days?  Doesn't make sense.  I thought you wanted to go out at least 6 months.  I suggested a year, and then you could play with it for a while without having to re-build or append next week.  Can you give us the ID and number of staff for each specialty?
0
 

Author Comment

by:candg
Comment Utility
Like i said it's just a dummy...i had a list of all 25..but i'm only using 2 staff members.
0
 

Author Comment

by:candg
Comment Utility
Name      StaffID      Active      Discipline
Amanda      AMAN      Yes      ST
Belinda      BELI      Yes      PT
Bell      BELL      Yes      OT
Carrie      CARR      Yes      ST
Dora      DORR      Yes      PT
Elyse      ELYS      Yes      OT
Fran      FRAN      Yes      ST
Heather      HEAT      Yes      PT
Jan      JANN      Yes      OT
Janice      JANC      Yes      PT
Katie      KATI      Yes      OT
La'Keisha      LAKE      Yes      ST
Mandi      MAND      Yes      PT
Mary Ann      MARY      Yes      OT
Molly      MOLL      Yes      ST
Sally      SALL      Yes      PT
Sarah      SARA      Yes      OT
Suzanne      SUZA      Yes      ST
0
 

Author Comment

by:candg
Comment Utility
It was formatted before I submitted it...sorry
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
OK, you should have your table of 36 x 15 minute appointments, something I called DailyAppts, your Table of Dates going out a year, something I called WorkDays, your table of Holidays and your table of Staff.  You can now remove the holidays and weekends from Workdays:

Delete * from WorkDays Inner Join Holidays On WorkDays.WorkDay=Holidays.Holiday;

then

Delete * from Workdays Where WeekDay(WorkDay) In (1,7);

You should now have a table with about 260 records.  Now Create table StaffAppts:

Select Staff.StaffID, WorkDays.WorkDate + DailyAppts.fldTime AS Appt,"" AS PatID, "" As Remarks Into StaffAppts FROM Staff, WorkDays, DailyAppts Order By Staff.StaffID, WorkDays.WorkDate + DailyAppts.fldTime;

This should give you a table with about 225000 records.  Tell me how it's going.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Sorry:

Delete * from Workdays Where WeekDays.WorkDay In (1,7);
0
 
LVL 34

Expert Comment

by:jefftwilley
Comment Utility
"""a option boxes for mon wed tues thur friday,"""
Don't think this will work if you want to select two at a time.
0
 

Author Comment

by:candg
Comment Utility
ur right....drop down boxes the way to go.
0
 

Author Comment

by:candg
Comment Utility
Grayl, is that the same thing that you wanted me to do earlier?
0
 

Author Comment

by:candg
Comment Utility
As in the previous Post?
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
candq: Correct.  This should generate a table of about 225000 records for all the working days in the next year for each staffID for each 15 minute block in the day.  With that table, using a few crosstab queries I have in mind to view the openings in your schedule, you will be able to determine if the response in generating the crosstabs is adequate for your needs.
0
 

Author Comment

by:candg
Comment Utility
Ok lets do it..
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
The biggest problem I see is being able to establish the initial fixed, twice per week appointment and have that carry on thru the next six months.  

In my system for a small clinic with 4 doctors, I select the doctor, whose appointment criteria are already in the table.  By this I mean what work days in the week, at what times starting, ending, and breaks, and using a crosstab, I can view the next 192 days across, time down per doctor.  Where there are openings (ie. no appointment booked) the system displays 0000.  Highlighting the first or successive zeroes will tell me that patient needs 1 thru 4 15 minute periods.  I capture that info using the various select properties of a control, and because crosstab queries are not updatable, I go back into the original table using VB code and 'update' the appropriate record, and then refresh the crosstab query.  Because these are usually oneoffs, I do not have to worry about recurring appointments.

However, in your case, because you are booking six months ahead, and the same number of periods per day on the same two days per week, by looking at the first week following today, you will be able to see what is open per staff member.  Another problem I forsee is five plus months into the program, when looking at the first week, you will see virtually all the appoinments taken, however, one week later, when the six months are up, the initial load will have finished and all the appointments will be available.  How you handle that crossover is still perplexing in my mind.  Anyway, that's what I see ahead.  Let me know when you are ready for a crosstab.
0
 

Author Comment

by:candg
Comment Utility
Is there a way to constantly update these loads...as a patient gets scheduled..?
0
 

Author Comment

by:candg
Comment Utility
For instance...say there is no inital load...except for each staff member, they get the standard set of times/dates inserted into dailyvisits.  Then from there I could have something that will allow me to schedule the patient for 6 months for a start time and and end time, for that staff member?  What do u think?
0
 

Author Comment

by:candg
Comment Utility
Gray still thinking?
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Yes, I'll get back to you when I have something.
0
 

Author Comment

by:candg
Comment Utility
No prob
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 44

Expert Comment

by:GRayL
Comment Utility
candg:  I was testing update time on a large table using the multi select property of a listbox and the results were very slow.  However, in playing around with it over the past two days, I have found a much faster method.  I probably won't be back to you until Monday, but stay tuned.

Ray
0
 

Author Comment

by:candg
Comment Utility
I'm Excited.  I did a work around..I used the 6 month appointments, converted the codes from numbers to Day Combonations.  Then used the query to update a table which has an autonumber appointmentID, then there is a details table to track further details.  And it's not exactly what I want but it works.  But I can't wait to see what you got for me.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
candg:  The problem with using a 6 month appointment schedule, today, is that tomorrow, it will be out of date.  I think you need at least a week, month, 6 months in advance of the six month ahead date for this to work.  Comments?
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
I 'discovered' that if you create the query in a form, and use the form to execute the update query, it is a 1000 times faster than trying to use a form to just select the time(s) of day and then run the query, referring to the form.  More later.

Ray
0
 

Author Comment

by:candg
Comment Utility
"candg:  The problem with using a 6 month appointment schedule, today, is that tomorrow, it will be out of date.  I think you need at least a week, month, 6 months in advance of the six month ahead date for this to work.  Comments? "

I'm really not understanding above comment.
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
"I 'discovered' that if you create the query in a form, and use the form to execute the update query, it is a 1000 times faster than trying to use a form to just select the time(s) of day and then run the query, referring to the form.  More later."

Do you mean that you get almost like a dynamic query builder embeded in the form and then have a backend query pickup the values and then it updates the appointment dates to the table?

0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
I'm saying use a full year appointment schedule and update it with the six month blocks.  Then after 3-4 months, archive the history, and add another 3-4 months to the appoinment schedule.

Your second part is correct.  Use the form to collect the info from the Patients table, PatID, VisitDays, select the visit times from a combobox, the StaffName (really StaffID from a combobox), and, on the click of an Update button, you would update the appointment table with the info after building the qeury in VB and executing it with a docmd.runsql method.  Can you tell me where you are at now.  What have you built - and provide names?
0
 

Author Comment

by:candg
Comment Utility
is there anyway i can email you a zip file?  I don't have a website
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
You can email it to me at the address in my profile.  I will then post in on my webpage - public.webbox.com/626170 - note no www.  
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
On second thought there is an address in EE that can now be used for this purpose.  The app has to be less than 4 meg.  Hold on and I'll try to dig it up.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Let me know when it is posted.
0
 

Author Comment

by:candg
Comment Utility
0
 

Author Comment

by:candg
Comment Utility
Please keep in mind that i am not professional.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
That makes two of us;-)
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Error:  "The database has been created with a newer version of Access that is not compatible with your version."  I'm running A2K and that's all I have.  What are you running and do you have an option of creating an A2K compatible version.  If so, replace the original in the EE post area and advise when done.
0
 

Author Comment

by:candg
Comment Utility
2k3
0
 

Author Comment

by:candg
Comment Utility
try again ray
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Is there anything in your Tools, Options, to allow A2K compatiblity?  I am currently doing work for a client who, I am sure has A2K3, and we don't have a problem.
0
 

Author Comment

by:candg
Comment Utility
0
 

Author Comment

by:candg
Comment Utility
I'm guessing you got it and it worked?
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Yes.  I had a bit of a finger problem but I can now open the mdb. Later.

Ray
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
I see you've been busy - 35 tables, 33 queries, 27 forms, 2 reports, 1 macro, 1 module.  I noticed your daily appointments table goes from 08:00 to 18:30 with no breaks.  Originally, you said 08:00 - 11:45 and 13:00 - 17:45.  For real?  I can't imagine anyone agreeing to work 10.5 hour day with no breaks.  Just some comments.

I guess the three specialties are Speech/Occupational/Physio?

I see you are lumping patients for Harlington and San Benito together, yet the clinics are several miles apart. I think you should get it working for each of the clinics and then think about an integrated solution.  Else, you need something like a ClinicID in all the appropriate records.

Table Holidays does not appear complete?

I see staff and doctors.  Your work is to schedule staff?  Doctors have their own system?

In tblTherapy, you have PatientID, Discharged, and Date_Of_DC, Which I imagine means DateOfDischarge.  If you just had the date, and the date was not null, that would say the Patient was discharged.  Redundant data.  

I don't understand PrevDate and NextDate in ClinicAppts.  You get those by a query.

In general you have to be more consistent with you naming conventions.  Remove all spaces, underscores, and special characters from names so you do not need to wrap names in square brackets.  Be consistent with the use of prefixes to identify object types.  Not all table names start with tbl and query names with qry or qmt, qxt,qin,qun etc.  You will find it is one of the best ways to ease the burden of troubleshooting.  In fact some queries start with frm?? - which I always thought to mean form.

More later tomorrow, got to run.

0
 

Author Comment

by:candg
Comment Utility
Comments:
"Originally, you said 08:00 - 11:45 and 13:00 - 17:45.  For real?"  -   I just didn't know how to have the break in there, and I really just left it there to reduce the time spent on one thing.   I planned on going back but not anytime soon.

Yes specialties are speech, occupational, physical.

The reason for that would be so that we could have one db for the whole system and therefore be able to print reports from the one db...but your right the lag would be horrible...maybe a replicated db?

Holiday's are not complete.  I haven't gotten a complete list..and i just wanted to see for my purposes (this database has not been implemented yet.)

My work is to schedule the staff, the doctors are there for demographic purposes for the most part.  

PrevDate and NextDate is my lack of experience.

Naming conventions, your right.  The only reason i used the frm was so that I could quickly group them when i needed to redesign a form.

thanks for all your comments, please keep them coming.

Chris
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Do you want to take a stab at 'cleaning up' the mdb or do you want to press ahead with what you got?  It's a ltlle like "Why do we always have the time to do it over, but never the time to do it right?".

It's just that the next qxtQuery needs a fixed format and I hate giving an answer only to have to go back and change things -  my T-Shirt says: "The reason I don't look busy is because I did it right the first time!"

Any way, enough preaching.  Next step?
0
 

Author Comment

by:candg
Comment Utility
No I want to purchase that T-Shirt too.  Well...lets do the fix...if anything I can get them to start entering data into excell...that will give us a week..
0
 

Author Comment

by:candg
Comment Utility
Grayl you thinking?
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
As usual, and things are getting hectic in my real life.  I'll try to find some time tomorrow.  
0
 

Author Comment

by:candg
Comment Utility
Hey no problem buddy..
0
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
Comment Utility
candq:  I'm back.  I've downloaded you mdb.  You've been busy.  Rather than take days to sort thru everything there here's what you need. First a form on which you select a specialty,(via a combobox).  Another combobox from which you select a Patient, but stores the ID.  Click a view schedule button, and a subform opens displaying the table ClinicAppts in a crosstab view from this query as the control source for the subform control:

TRANSFORM First(Nz(a.PatientID,"0000"))
SELECT a.StaffID, Format(a.Appointment,"Short Time") AS ApptTime
FROM ClinicAppts AS a INNER JOIN Staff AS b ON a.StaffID = b.StaffID
WHERE (((a.Appointment) Between Date() And Date()+30)) AND b.Specialty = "Speech Therapy"
GROUP BY a.StaffID, Format(a.Appointment,"Short Time")
PIVOT Format(a.Appointment,"yy\/mm\/dd\/ddd");

I am assuming you have 'cleaned up' the naming convention.  In addition, when you append records to the ClinicAppts table with your query, change "" to Null for PatientID.  You need that for the Nz() function to work correctly.  When you get this working, you will get a feel for where I am going.  Then next step will be to click on a datetime and highlight 1 thru 4 of the zeros. "Speech Therapy" will ultimately be replace by Forms!myForm!cboSpecialty.  For this specific case with only two records for staff, the speech therapist is selected, in this case, 'CGUT'.  You can remove ' AND b.Specialty = "Speech Therapy"' from the crosstab to show both your staff.   When you get further down the road, and the staff table is filled in, the crosstab will show all the records for the staff who match the specialty on the in cboSpecialty.  It should be relatively straightforward to then pick two days, in consultation with the patient to lock in the next six months.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
candg:  You thinking?
0
 

Author Comment

by:candg
Comment Utility
As usual.
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
There's an echo in here;-)
0
 

Author Comment

by:candg
Comment Utility
haha on purpose
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
What's your purpose?  4 days and not a peep!
0
 

Author Comment

by:candg
Comment Utility
Just been busy.  I looked at it and I still haven't gotten that form up and running yet.  This weekend has been nuts, going to visit mother in law.  So...tonight i'll take get that form up and i'll write back.
0
 

Author Comment

by:candg
Comment Utility
thanks looks good.
0
 

Author Comment

by:candg
Comment Utility
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
Thanks, I've had a problem with my ISP and no EE Emails.  Now fixed.  I'll check in on the above.

Ray
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now