Page 3 of 7 FirstFirst 1234567 LastLast
Results 31 to 45 of 92
  1. #31
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Thinking about it some more, if a clinical domain can be related to any issue, cause, etc., why not just put all of those items into 1 table with a field to distinguish to what category (issue, cause, action etc.) the item belongs? This will simplify the form since you can have each list box reference the same table and just filter each based on the category.

    tblItems
    -pkItemID primary key, autonumber
    -fkCDID foreign key to Clinical domain table


    -txtItem
    -fkCategoryID foreign key to tblCategory

  2. #32
    Duncan is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Oct 2010
    Posts
    71
    I agree. I have attached my full working document for Clinical Domains, issues, cause etc. will this fit the design. Note in the spreadsheet that each Clinical Domain has a completely different set of issues, causes etc attached to it that the user will only need to see if selecting the associated Clinical Domain from a list box.
    In your post No. 15 you summed it up ‘all issues/causes would go in 1 table, you can add 1 field to identify to which clinical domain they belong’. This is why I thought we may need to add CDID beside each issue, cause etc so you have an identifying number to help with separating them out into their specific Clinical Domains.

  3. #33
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Please ignore my suggestion in post #31, it won't work because some causes apply to multiple clinical domains. That will require a junction table. The same appears to be true for the other categories. So if you want to keep separate tables, you will need a corresponding junction table for each as well to link with the appropriate clinical domains. Additionally, you will need to link those junction tables back to the client assessments.

    I have migrated all of your issues, causes, etc. from the spreadsheet you provided and linked them to their respective clinical domains in the attached database. Please review the relationships to see if you understand what I have done.

  4. #34
    Duncan is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Oct 2010
    Posts
    71
    I completely understand what you have done. It is excellent it’s exactly what I had in mind. It must of taken ages because you have not repeated any say ‘issues’ just had the one and made multiple links to it and the associated Clinical Domain. Please tell me there was a quick way to do it in Access instead of trolling through the Excel spreadsheet and looking for duplicates then manually linking back to CD. The Appendix grouping is also excellent. You are a champion.

  5. #35
    Duncan is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Oct 2010
    Posts
    71
    For future amendments/evolution of the database and my education how did you transform the Excel data into the Access database you posted back?

    If you look at Clinical Domain 15 (Observations) and 24 (Other) they have free text fields in them. Will they fit in the current structure? E.g., with CD 15 (Observations) the issue, cause and clinical indicators have free text fields (as the reasons for needing observations are too vast to document) in them while the actions considerations and appendix have predetermined data. I’m thinking that I may not be able to include them in the original CD list, but have a separate table linked back for the actions considerations and appendix and client (because of the free txt input fields). What do you think? There could be a small separate button on the form for Observations and Other that when clicked opens a smaller form for the data input (I can do this) or could be VBA coded to open when selected from the CD list (I think – after update to open form?).

    Same for Medications (ref to attached) as the field structure is different to all other CD’s. What do you think? As all clients have medications, medications can have its own place on the main form with a completely different table and input structure or as the list will be quite extensive a new form could open just for medication input that loads back into the main form though a qry and a datasheet subform (I can also do this). What do you think?

  6. #36
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    For the observations, you would just have a related table to enter however many observations for the particular assessment. The structure would just not reference a table with predefined values. Medications can mimic the other tables since you can have a table that holds all possible medications. In the junction table related to the client's assessment you would have a field for the dosage if you so choose. As to the other fields you referenced in your medication spreadsheet, you would probably need to break some fields into separate table where there are many items that related to the medication. You might do the same just for the name of the medication because technically you have 2 names (trade and generic), so a one-to-many relationship, but some might consider that as taking normalization a little too far.

  7. #37
    Duncan is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Oct 2010
    Posts
    71
    G-Day. Have I overstayed my welcome with this thread and your assistance? I'm conscious of same and wondering if you are willing to help with what I perceive to be the last bit (how to manipulate the rationalized tables and data making them accessible in a form to the end user, I take it is not as simple as just After update coding in the Clinical Domains list).

  8. #38
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Have I overstayed my welcome with this thread and your assistance?
    No, I have just been real busy this weekend on other things. Hopefully I will have some time tomorrow to get a form started for you.

    I did forget to answer your question about how I moved the items from your spreadsheet to the tables. The first thing I did was to import the spreadsheet as a new table in Access. I then created a series of append queries to move the information into the appropriate tables. I did have to do some manipulation of the appendix data to break it out into the categories before running the append queries that took a little while.

  9. #39
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I've attached a database with a couple of forms that navigate the user through an assessment. I only set up the list boxes for the Issues and Causes; setting up the others will be essentially the same. The first form (frmClientAssess) just saves the assessment date and client in the tblAssessment and then opens the detail form (and populates some key controls). When a CD is chosen, queries are setup for each of the list boxes that filter each to the related items (issues, causes etc.); see the after update event of the CD combo box. Behind the Save button on the detail form is the code that saves the CD and associated items from each of the list boxes (I basically copied the code from the previous database I posted with a few slight name changes since I was not consistent from the older DB to this one--my fault). When the user has finished entering a CD, the code prompts the user if they have more to enter and repeats the process until the user says No. When the user says no, I just close the form. You will probably want another form to open to enter the medications and observations since those will have to be treated differently as discussed earlier.

  10. #40
    Duncan is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Oct 2010
    Posts
    71
    Thanks for the forms. I started a new job this week so will endeavor to finish the coding for Clinical Indicators etc then I can and post back the finished product for you to look at, if this is Ok. I’m sure I will have a question or two. Again thank you for the comments in the code as it makes it easier for me to understand what I’m actually looking at.

  11. #41
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Congratulations and best of luck in the new job!

    If you have questions, please do not hesitate to ask.

  12. #42
    Duncan is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Oct 2010
    Posts
    71
    I have learnt that middle management in a hospital is no fun and it has only being for one week! Only for six months though. Was able to replicate the code to varying success, still working on it.
    My current burning question is: how do I get the stored data in the tables into one meaningful table that has Client ID, Client Name, Clinical Domain, Assessment Date, Issue .........Actions. I’m trying with a query however as the information is stored as numbers I’m having trouble converting it into the words. Also say someone chooses one Issue and Two causes getting the two causes in the same record and cell (to use an excel description) with the one issue. This will obviously be the foundation for reports etc. Tacked on to this is: would the causes in this example be able to be edited and changed by the user down the track while keeping the issue.
    For example re above; if one of the causes is no longer required for that issue and all the rest is still relevant (clinical indicators, actions considerations etc) would the user be able to go to this record and change the assessment date and the cause while keeping the rest of the data thus not creating a whole new record (just updating an existing one) and having to put all the data in again. Like a working record for that clinical domain that could be repeatedly changed/updated (I know this can be done with multi-select combo’s but not sure with list bxs)

  13. #43
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    My current burning question is: how do I get the stored data in the tables into one meaningful table that has Client ID, Client Name, Clinical Domain, Assessment Date, Issue .........Actions
    You would never duplicate data in another table, you would just use a query to bring it together. You can use a report to list the causes on separate lines. But if you really want to have the causes (even if there are many) on the same line, then you would need a custom function that concatenates the various related values into a single string. Allen Browne has just such a function on his site. Once you concatenate them, you would not be able to edit the individual records from that field that has the function (you still would be able to edit them via a form that lists each individually)

    Regarding your last question, you can delete records as needed from an evaluation without impacting the other records related to that evaluation.

  14. #44
    Duncan is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Oct 2010
    Posts
    71
    I thought I had it all under control but try as I might I can’t do what you suggested in you last post. Essentially I have three issues I’ve spend the last week trying to nut out.
    1. The Appendix list box and showing the Appendix data with the Appendix Category data in the list box. I can get one to show like the rest but not too then getting the appropriate thing recorded in the table ClientAssessmentCdAppendix.
    2. Generate a query to pull all the data together. This ended up like a dogs breakfast. The issue is all the data is stored in numbers and I must be thick B/C I’m having troubles getting them into words again also
    3. Combining the data together in the same ‘cell’ if two things are selected. I went to the website (which is great by the way) and tried the query way and code way but both are a little over my head to say the least and just end up with errors upon error upon code compiling errors (I have been debugging all week!).
    Any chance you would be able to help no rush and only if you have time. I got all the other things working by copying and adapting your code. The medications, other, and obs, for ease of me doing them without asking for help I think I may have either new forms open from buttons or pages behind the main page (I have created the tables for same linked to pkAssesscdID (ref to relationships). On an unrelated matter than thank you for showing me how to name the fields. I now see the value in naming them with txt, pk, fk etc



  15. #45
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    I kept getting an error when I tried to unzip the DB you posted. Can you repost it?

Page 3 of 7 FirstFirst 1234567 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 29
    Last Post: 08-16-2011, 05:52 PM
  2. Replies: 1
    Last Post: 03-24-2011, 07:09 AM
  3. Replies: 7
    Last Post: 10-20-2010, 04:08 PM
  4. Replies: 0
    Last Post: 08-24-2010, 06:38 PM
  5. Replies: 0
    Last Post: 12-16-2009, 01:14 PM

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