Link to home
Start Free TrialLog in
Avatar of candg
candg

asked on

6 months of Visit Information

I have 6 months worth of dates for each client.  I would like to keep track of the missed visits as well as the amount of time they visit for.  
So the day it starts is every day that the patient comes and is already in tblvisits, the appoints are in 15 min increments, I wouldn't know how to deal with breaks, and the last visit can be at 6pm.  No visits on the weekend.  I would like to have a datasheet, or a crosstab query that would show me this.

Thanks Chris
Avatar of GRayL
GRayL
Flag of Canada image

Assuming every day starts at 09:00 to 12:00 and 13:30 to 18:00 you would need an appointment table representing this work day in 15 minute intervals.  Please confirm your actual start and end times as well as the noon break and any other breaks you may have in the day.
Avatar of clarkscott
I've created Physician Management systems.  I have a setup form which allows the office to create their own Appointment database.  They determine the default visit time (15, 30 min, etc.), the office start and stop time (daily), and the number of records to create (based on month and year).  When they click the button, the system creates the Appointment records.  The software then manages what info (patients) are entered and for what time appointment.
The system also calculates the amount of time required for each patient and blocks off anyadditional appointment slots with '<<<<<<<<<<<<<<'  The system also includes a 'dual appointment' function that inserts appointment records 'on the fly' so they can squeeze appointments in when necessary.

If breaks occur, they simply enter it (as a patient) and block off the time.

Scott C.
Avatar of candg
candg

ASKER

That is EXactly what i want...but I don't really want to pay for it....lol..

Start Time is 8:00am -  12pm     (**Lunch Break**)  1pm - 6pm.  

I wasn't suggesting buying it.... I was simply suggesting what I did to deal with it.  :-)
Scott C
Avatar of candg

ASKER

No thank you i really appreciate it...and that is exactly what I would like to do that would be great but i'm not that up to your level.  
Avatar of candg

ASKER

Out of curiosity clarkscott, how did you graphically represent the patients, would you be against showing me any screenshots so that I could look at and maybe get an idea?
Further....
My appointment table has fields for Visit Status (closed, missed, etc.), Patient Record (primary key to Patient Table), memo fields for quick comments.  

My Payment table relates to the Appointment record, along with relationship to Patient Record.
Same with the Charge Table - related to both Appointment record and Patient Record.

Scott C
Open my website - processcontrolsoftware  and click  CASE STUDIES.  - Click CHIROPRATOR OFFICE MANAGEMENT SYSTEM.  You will see screen shots there.

http://www.processcontrolsoftware.com/

Scott C.
Avatar of candg

ASKER

Nice website and App.  Thanks.. I get an idea of what I'd like to do.  
Avatar of candg

ASKER

Any more info/examples you would like to offer would be greatly appreciated...
View my website and check out other CASE STUDIES.  Maybe some form examples of other apps will give you some ideas.

Scott C.
Avatar of candg

ASKER

GRAYL are yo ustill there?
Here.  From the dialog, I thought you had things in hand.  When you say 'last visit 6 pm you mean ends at 6 pm?  So the last appointment time would be 17:45?  
Avatar of candg

ASKER

yeah that will work 5:45 right?
Sorry, being ex-military, I only know a 24 hour clock;-).  To review, you have your table with clientID's , and two dates per week going out 192 days from the client's  initial start date.  You now want to create an appointment table that will take the times from your daily schedule and add them to the dates from which you can select from one to eight 15 minute periods?  Rather than me put words in your mouth, I'll stop here and ask you to describe what you want, given what you've seen in Scott's website.
Avatar of candg

ASKER

Exactly..damn your good.
ASKER CERTIFIED SOLUTION
Avatar of GRayL
GRayL
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of candg

ASKER

Sounds absolutely wonderfull man I appreciate your help soooo much...  Is there anyway we can just give a "holiday" entry or "Employee Out"?

 
Are you saying you need to give your staff holidays and vacation?  Each of  your staff has clients with their individual codes.  Is that what you meant by Employee Out?  You are not very forthcoming with information.  
Avatar of candg

ASKER

Ok...yes staff need both Holiday and Vacation...but I'm saying that it's more like a reason for missed.


ClientID     ApptID       Date               Appt Time       Status of Visit           Reason                Remarks
  1 Chris     1235       05/24/2006          8:30                 Missed                Holiday                  Thanksgiving


ClientID     ApptID       Appt              Appt Time              Status of Visit           Reason                 Remarks
  1 Chris     1234       05/24/2006         8:30                     Missed                Employee out          Bob out ill

Shouldn't there be a staffID?
The first shouldn't be a reason for Missed, it should never have been scheduled.  The second is valid, assuming Bob is staff.

BTW, this Access forum is here to provide assistance to people who are having specific problems with the application; we are not here, or at least shouldn't be, to 'design' systems - at least I'm not.  I do this for a living, and although I am sure I know what you 'utlimately' would like to wind up with - for zero cost - I can't afford it.
Avatar of candg

ASKER

It's just the last piece to my puzzle...mr gray  but i understand thank you for your help it's been greatly appreciated.
candq:  Thanks, glad I could help.  I am willing to give you the 'piece' but one 'piece' seems to lead to another.  If you would be specific about what you want,....
Avatar of candg

ASKER

OK...what i need to be able to show in a report and in a form the clients visits (dates), whether or not they came or not, the employee they saw, the service they recieved.  I figure if I could just add one of those I could figure it out.  I mean we have the dates...are you able to add more column headers to the crosstab query?  Because i'd like to be able to show that






            Monday Attended    Tuesdaay  Attended    Wednesday Attended   Thursday Attended      Friday Attended.  


ClientID      ClientName     WeekStart       Status of Visit      Monday  Attended?      Tuesday    Attended?    Wednesday   Attended?    Thursday Attended? Firday A
1      Adam Baker    5/8/2006                        5/10/2006  Attended            
Avatar of candg

ASKER

grayl are u still around?
candq:  Still here.  Sorry, I had to get my 'real' work done over the past several days.  I am not sure what you need now.  We had the crosstab query from the original question which listed by week per client the five working days and which two of the five had been given for a visit - out to 192 days from the initial start date.  You start this question by saying you need an appointment book - for all intents and purposes.  Later we discover staff are involved - still don't know how many staff or clients.  Once a client starts with a staff member, do they stay together for subsequent visits?  Like I said previously I don't want to put words in your mouth so I invited you to elaborate.  In you second last post you look like you are back to the Original crosstab with a modification.

It is to say you cannot 'add' fields to a crosstab.  You can concatinate information but you cannot add fields.  Once the situation becomes historical, that's historical, not hysterical, why do you want to continue to see the crosstab format?

As to a typical table size, if you had several thousand clients , and 6 staff working 260 days per year your appoinment book table has 36 slots per day x 6 staff x 260 days per year = 56160 records per year.  Still no idea typically how many visits a client makes.  Do you really book a client 192 days in advance?  Etc. etc.

What is the next step?  
Avatar of candg

ASKER

Well here's how it works.

Client gets referred, they are input into the system. We get all their stats, addy,phone etc.  After that then the client comes in to get evaluated to see whether or not the client needs our services or not.  If they do then 6 months of dates twice a week and can be seen by as many of our employees as necessary.  They will be seen for 6 months or until they cannot pay anymore.  Should the employee recommend that the client may need to stay for another six months.  The patient can be seen for an infinite amount of time.  The actual visits entail a scheduled start time and an end time.   Whether or not the client came in needs to be recorded.  In addition to that we conference with an outside agency about the client and get monthly reports about that for their record.  

Thanks GRAYL
<still don't know how many staff or clients.>  You are a tough one to get info from!

How many staff, how many clients?  Do you have a list of statutory holidays - during which I assume no one is working?  
Avatar of candg

ASKER

Number of Staff is unlimited, clients as well.  For Holidays I'd like to be able to have a little leeway but they aren't really necessary....just the weekends no one works.
Avatar of candg

ASKER

Grayl are you still around
I'm still trying to figure out how to work with two infinities.  With unlimited staff and unlimited clients, it is impossible to devise a meaninglful schedule.  I imagine this is required for a specific clinic in which I would think there are limits.
Avatar of candg

ASKER

Would it be easier if I came up with a list of holidays?
It would be easier if you were able to tell me about how many staff and how many clients.  If you have 1000 staff and 100,000 clients, Access is not the app for you.
Avatar of candg

ASKER

oh no it's at the most 60 staff and at the most 500 and i'm pushing it.
Avatar of candg

ASKER

500 patients sorry
Avatar of candg

ASKER

Clients / patient samething
If you have Excel, use it to build a table of sequential dates for the next 2 or so years and Import the spreadsheet into a table named WorkDates with a field named WorkDate.  I suggest Excel because it is easy to create a column of sequential numbers that can be turned into Dates.  Next you need a table DailyAppts with one field DailyAppt with times starting at your start work time (08:00?), in 15 min increments up to the lunch break, then after lunch until 18:00.  You might just as well create a table Holidays, with two fields, HolName, and HolDate.  Delete all the dates which are weekends and Holidays.

Delete * from WorkDates where Weekday(workDate) in (1,7);
Delete WorkDates.* from WorkDates Inner Join Holidays on WorkDates.WorkDate=Holidays.HolDate;

I presume you already have a table of Staff:

Staff
====
StaffID  - Primary Key - text?
FName
LName
Add1
Add2
City
Region
PCode
Phone
etc.

I presume there is a similar table for Patients, in which the PatientID is probably the Patient's Medicare number.

Now do a Cartesian Join on WorkDates, DailyAppts, and Staff

SELECT a.StaffID, b.WorkDate + c.DailyAppt AS Appt, "" AS PatientID, "" AS Remarks FROM Staff AS a, WorkDates AS b, DailyAppts AS c WHERE b.WorkDate BETWEEN Date() and Date()+365
ORDER BY a.StaffID, b.WorkDate + c.DailyAppt;

This recordset will give you a rather large table with the next year of appointments for each staff, and a field into which you can insert a PatientID and Remarks.  I assumed the PatientID is text as is Remarks.  If you are satisfied with that result, you can turn the query into a make table query:

SELECT a.StaffID, b.WorkDate + c.DailyAppt AS Appt, "" AS PatientID, "" AS Remarks INTO ClinicAppts FROM Staff AS a, WorkDates AS b, DailyAppts AS c WHERE WorkDate BETWEEN Date() and Date+365
ORDER BY a.StaffID, b.WorkDate + c.DailyAppt;

When you get that built, I think it will be time for a new question as I am sure when you see what you wind up with you will have a load of them;-)
Avatar of candg

ASKER

haha thanks grayl
Avatar of candg

ASKER

One question does it matter if I change the times from military to non military
From 24 hour clock to 12 hr clock:  No.  Nothing military about a 24 hour clock.  Most of Europe runs on it.  Keep smiling!
candq:  Do you see where I am going? I hope I gave you enough to get you started. I'm an Albertan, living in Montreal, and it is now one minute from game time.  
Avatar of candg

ASKER

Alright.  Well I just got let go.. hahaha...damn that sucks... they don't even realize what I was about to present to them....  But i plan on continuing this project  Yeah I already put together all the things you told me to...and your right i have some questions for a new post.  Thanks grayl
candq:  See you in the next post.  I'm impressed to got to the ...all the things....