Patient appointment schedule

[AndyRob1973]

Hello,



I'm in the process of building a database for patient appointments.

There will be a table with a series of appointments eg

1 First appointment (Day1)
2 X ray (Day 2)
3 General Examination (Day 3)
4 Blood test (Day 7)

Some patients will need all the appointments and some will need only some.

I would like to be able to add a patient to this schedule on a form and indicate if they need all the appointments but if not, select individual appointments.

So my w questions are:

1) What is the best way to set up the relationship between the patient table and the appointments table? Would it be a cross join so that all patients by default are linked to all appointments?

2) If anyone has any ideas on the best way to set up a form to make the appointment selections I would be obliged!

Thankyou,

Andy

[ranman256]

a list of Hours in a listbox.(minus those already booked)
user dbl-clicks to 'take' those hours. An query adds the patientID & Hours to the tAppt table.
dbl click on Exam name (from a list), to add that too.

[Micron]

You don't specify hours or if it's just appointments, so assuming the latter, tables something like (omit the parts in parentheses for field names)

tblPatients		tblApptType		tblAppts
PatientID (PK, auto number)		ApptID (PK, auto number)		ApptID (PK, auto number)
Fname		ApptType		PatientID (fk from tblPatients)
Lname		Description		ApptID (fk from tblApptType)
Address		etc.		ApptDate
DOB				Location
etc.				etc

Your form listbox contains the appointment types and you either select all or just those that apply. There are lots of examples of how to include an ALL feature in listboxes.

[AndyRob1973]

Thanks ranman and Micron for your replies,

I’ve had a rethink about how to ask my question. Originally I was trying to give a really simple example to set me on my way. I’m not experienced in this and am feeling my way through. I’ve attached an excel spreadsheet to illustrate. Is this allowed?

In reality, I will be given a an excel spreadsheet with a table like below (It will have a lot more data in than this but I’ve made this to help explain the principle behind my question). It shows the activities that will be carried out at each appointment, how much they cost and how long they take.



I need to import this information in to Access and book patients onto the appointments and eventually be able to report on things like the costs and time spent.

This is obviously a cross tab matrix. Am I correct in thinking that I would be better splitting this into a relational database? If so, how does the diagram below look (bearing in mind that the database will evolve, I feel I need to set it up correctly from the start)? NB The crows feet mean ‘1 to many’.



I may be going about this the wrong way so any advice would be appreciated.

Thank you

Andy

[ranman256]

link the spreadsheet as an external table.
then run 7 append queries to add the data, (1 Appt column per qry) ,to the proper tables.

[Micron]

I'm going to throw a lot of links at you at once because I think you need them. Make sure you understand normalization, and find your own links for that if these are not helpful enough. I say that because you've already started off on the wrong foot so to speak, as your inclination is to design tables as you would a spreadsheet. One test of this is to answer the question "what happens if I need to add a different appointment type?". The answer would be - add a field to the table then redesign every query, report and form that uses it - not good.

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Important for success:
Naming conventions - http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names
- http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://www.utteraccess.com/wiki/Autonumbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields - http://www.mendipdatasystems.co.uk/m...lds/4594468763

[AndyRob1973]

Thank you both,

The slight problem I have is that the spreadsheet I am given will always come to me in the format it is.



But like you say it, each time I receive it there could be many numbers of appointments and many types of activities. Hence my thinking that I should import the table into access and then manipulate it into the structure in my diagram.



I’ll do some digging/experimenting and thanks again!
Andy

[Micron]

Then link but transpose per post 5. Just make sure the tables are properly designed per post 6.

[ssanfu]

After you have looked at the links (a couple of times) Micron provided, you should design your tables/relationships on paper, whiteboard (I use my office window), cardboard, etc. This makes it easier to see the overall design and to be able to make changes.
Some suggestions:
Use only letters and numbers in object names (exception is the underscore).
Do not use spaces in object names (objects are fields, tables, queries, forms,reports)
Do not use punctuation or special characters in object names.
Do not use Multi-value fields, Calculated fields or Look up FIELDS in tables.
Do not begin an object name with a number.

I use prefixes for objects frm for forms, qry for queries, rpt for reports. (examples: frmAppoinments, qryActivityList, rptActivities)


Think about the object names. For example: "Required".....What is required? Maybe use "IsRequired". But it still doesn't describe WHAT is required.


Oh, and your current design doesn't have anywhere for the link to the patients ID nor any date fields.......



Good luck with your project........

[accesstos]

Andy,

I found your case interesting and I made a sample database (before your last posts) based on this schema:



I think that it's pretty close on your needs and I believe that you will find it helpful for your project development.

Cheers,
John

[AndyRob1973]

Andy,

I found your case interesting and I made a sample database (before your last posts) based on this schema:



I think that it's pretty close on your needs and I believe that you will find it helpful for your project development.

Cheers,
John

Thankyou John!

I'll have a good look through that!

[AndyRob1973]

Hi John

Thanks again, I found your database very useful.

Reading back, I don't think I explained myself very well (I'll try to paste the attachments in this text but if not i'll just attach them)

Here is a simplified example of a patient study schedule table that I will receive (attached as original_table). I will need to represent and use in access. Every patient that we recruit onto the study will have all the appointments, and within each appointment will receive the indicated activities. In reality I will be given numerous study schedules all with different numbers of appointments and activities.



I've tried to represent it with this relationship (attached as 'Relationship1')



I think I'm ok with what I've done so far (unless I'm mistaken)

My next two hurdles are:

1) I have created a Patient table (tbl_Patient) and when I adda new patient to this table I would like them to ‘inherit’ the schedule ofappointments and activities in (‘tbl_StudySchedule’) . To do this I’ve extendedthe relationship below (attached as ‘Relationship1’) and any Patient_ID that is in ‘tbl_Patient’ thatis NOT in ‘tbl_PatientSchedule’, I run an append query to add them into tbl_PatientSchedule

with the schedule that is in ‘tbl_StudySchedule’.Is there an easier way to do this?



2)

Now it has occurred to me that the dates that I will beinputting in ‘tbl_PatientSchedule’ relate to the patient’s appointment ratherthan each of the activities on that appointment. I’m trying to think of a wayof doing this and I think I may need to alter the model. Can anyone suggestanything?



There is always the possibility that I’m coming at this fromthe wrong angle!



Thanks

[AndyRob1973]

Hi JohnThanks again,


I found your database very useful.

Reading back, I don't think I explained myself very well (I'll try to paste the attachments in this text but if not i'll just attach them)


Here is a simplified example of a patient study schedule table that I will receive (attached as original_table).
I will need to represent and use in access.
Every patient that we recruit onto the study will have all the appointments, and within each appointment will receive the indicated activities.
In reality I will be given numerous study schedules all with different numbers of appointments and activities.

I've tried to represent it with this relationship (attached as 'Relationship1')

I think I'm ok with what I've done so far (unless I'm mistaken)

My next two hurdles are:

1) I have created a Patient table (tbl_Patient) and when I add a new patient to this table I would like them to ‘inherit’ the schedule ofappointments and activities in (‘tbl_StudySchedule’) .
To do this I’ve extendedthe relationship below (attached as ‘Relationship1’) and any Patient_ID that is in ‘tbl_Patient’ thatis NOT in ‘tbl_PatientSchedule’, I run an append query to add them into tbl_PatientSchedulewith the schedule that is in ‘tbl_StudySchedule’.
Is there an easier way to do this?

2) Now it has occurred to me that the dates that I will beinputting in ‘tbl_PatientSchedule’ relate to the patient’s appointment ratherthan each of the activities on that appointment.
I’m trying to think of a wayof doing this and I think I may need to alter the model. Can anyone suggest anything?


There is always the possibility that I’m coming at this fromthe wrong angle!

Thanks

[accesstos]

Hi Andy!

Your schema seems to be on the right way, but, you don’t need to repeat all that combinations of appointments-activities in the patient’s schedule table if you are not going to use them all – on the contrary, you shouldn’t. Tables are places to keep existing objects and events that have taken place (even if they refers to the future), not possibilities of them. That’s why we call them data (δεδομένα in Greek). The presentation of the related data is job of the queries.

As suggestion, I attach a new sample database, and, I will be glad if you'll find it useful as well. It's not something perfect but I think that it’s a good source for inspiration.

Cheers,
John

[AndyRob1973]

Thank you John,

I’ve had a really good look through your database and have taken on lots of ideas which I’m going to use in my database.

In doing so it has help me to get a clearer picture of what I’m trying to do and the best way to explain it. I hope you don’t mind me appearing to go round in circles with my questioning! I think this is the most succinct (so far!) way of explaining it. (I may post this in the databse design part of the forum - is this allowed?)

In your example database is there the option to record whether each activity is required for each appointment? This will be predetermined as part of the study.

For each study I will receive an excel table like the two examples below (simplified version of a real one). For now I’m not thinking about how I will import it into Access but will do once I’ve got the Access principle sorted. I know that I will have to split the tables into normal form.

Every patient who is recruited to each study WILL attend all the appointment (cycles) and all the specified Activities that pertain (ie the Y’s) to that appointment (the ‘Y’s will be represented in tbl_Patient_study_appointment_activity). Each study could have a different number of appointments and activities.

For example, every patient who is recruited to the Diabetes study will have all 7 appointments (cycles) and at appointment 3 will undergo ‘Medical History’ and ‘ECOG Status’.





Each patient could be on more than one study.

I have used a variety of junction tables to achieve what I think does the job for me, although I do wonder if I am over complicating it. Could anyone help or give feedback? (I have been reading up about ternary relationships but don’t fully understand how they work. Maybe these could work?)




Once I am satisfied that this schema does will work. I need to find a way of when I add a patient to a study, that they inherit the pre-determined appointments and activities.

Am I right in saying that this would be a series of append queries? Or could I use a form which when I add a patient to a study they automatically get populated in the ‘tbl_Patient_Study_Appointment’ and ‘tbl_Patient_Study_Appointment_Activity’

Once I’ve done that I need to work out how I will upload a new study and schedule into the database.

If you or anyone could help I would be most grateful. I know there are some gapping holes in my understanding of Access.

Thank you for your patience!

PS I'm using Access 2010