Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    60

    Patient appointment schedule

    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

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,795
    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.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,563
    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.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    I’m telling everyone it's good to eat dried grapes. It’s all about raisin awareness.

  4. #4
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    60
    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.

    Click image for larger version. 

Name:	11.png 
Views:	86 
Size:	7.6 KB 
ID:	44027

    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’.

    Click image for larger version. 

Name:	222.jpg 
Views:	87 
Size:	77.0 KB 
ID:	44028

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

    Thank you

    Andy

  5. #5
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,795
    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.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,563
    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
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    I’m telling everyone it's good to eat dried grapes. It’s all about raisin awareness.

  7. #7
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    60
    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.

    Click image for larger version. 

Name:	11.png 
Views:	84 
Size:	7.6 KB 
ID:	44029

    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.

    Click image for larger version. 

Name:	222.jpg 
Views:	84 
Size:	77.0 KB 
ID:	44030

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

  8. #8
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    8,563
    Then link but transpose per post 5. Just make sure the tables are properly designed per post 6.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    I’m telling everyone it's good to eat dried grapes. It’s all about raisin awareness.

  9. #9
    ssanfu is online now Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,259
    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........
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  10. #10
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    506
    Andy,

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

    Click image for larger version. 

Name:	schema.JPG 
Views:	80 
Size:	23.0 KB 
ID:	44034

    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
    Attached Files Attached Files

  11. #11
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    60
    Quote Originally Posted by accesstos View Post
    Andy,

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

    Click image for larger version. 

Name:	schema.JPG 
Views:	80 
Size:	23.0 KB 
ID:	44034

    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!

  12. #12
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    60
    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















    Attached Thumbnails Attached Thumbnails Original_table.jpg   Relationship 1.jpg   Relationship2.jpg  

  13. #13
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    60
    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
    Attached Thumbnails Attached Thumbnails Original_table.jpg   Relationship 1.jpg   Relationship2.jpg  

  14. #14
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    506
    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
    Attached Files Attached Files

  15. #15
    AndyRob1973 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    May 2020
    Posts
    60
    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’.



    Click image for larger version. 

Name:	Excel tables.jpg 
Views:	55 
Size:	106.4 KB 
ID:	44236

    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?)

    Click image for larger version. 

Name:	schema.jpg 
Views:	56 
Size:	79.9 KB 
ID:	44237


    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

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Patient data
    By osiroky in forum Reports
    Replies: 13
    Last Post: 01-07-2020, 09:28 AM
  2. Patient Database, linking a patient to several diseases
    By GonSantos in forum Database Design
    Replies: 11
    Last Post: 01-23-2019, 01:57 PM
  3. Patient Report printing
    By vijay in forum Reports
    Replies: 2
    Last Post: 05-09-2014, 05:33 AM
  4. Patient database
    By Addanny in forum Access
    Replies: 4
    Last Post: 10-02-2013, 06:59 PM
  5. Patient Database
    By labadee in forum Database Design
    Replies: 1
    Last Post: 06-27-2010, 10:53 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums