Page 2 of 7 FirstFirst 1234567 LastLast
Results 16 to 30 of 92
  1. #16
    Duncan is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Oct 2010
    Posts
    71
    - Then with respect to the clinical indicators, does each issue/cause have specific clinical indicators that it relates to?



    No. Clinical Indicators are treated like the rest of the fields (issue/cause/Actions/ Appendix). That is, is selected randomly (multiple ones at once if required) within that specific Clinical Domain that is relevant to clients presenting issues.

    - No, all issues/causes would go in 1 table, you can add 1 field to identify to which clinical domain they belong.

    That's what I have come to understand. Find attached my attempt to represent this for all fields using Clinical Domains - Self Care and Mobility modeled from your examples. Cheers

  2. #17
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    OK, now I am confused.

    You said this earlier:

    Each client can be related to any number of the Clinical Domains. Within each Clinical Domain is Issue, Cause, Clinical Indicators, Actions Considerations etc (Field Headings) .
    Now you say this:
    That is, is selected randomly (multiple ones at once if required) within that specific Clinical Domain that is relevant to clients presenting issues.
    So does a client have many issues and then related to each of those issues are there one or many clinical domains?

    Without a clear understanding of how your data is related, it is difficult to help. Can you explain things in terms of the process you are trying to model rather than in terms of Excel sheets, forms, dropdowns etc.?

  3. #18
    Duncan is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Oct 2010
    Posts
    71
    Please find attached a scenario based explanation of what I'm trying to achieve with this particular aspect of my database. Cheers

  4. #19
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Your attachment helps. I would still have the separate tables for clinical domains, issues, causes etc., but relating them to the client and their respective admission event is what is needed. I assume that a client can be admitted on multiple occassions and/or have many assessments, I don't know if you want to track both the admissions and assessments or just the assessments since those are directly related to everything else we have been discussing. For illustration purposes, I'll just show the assessments

    tblClient
    -pkClientID primary key, autonumber
    -txtFName
    -txtLName

    tblClientAssessments
    -pkClientAssessID primary key, autonumber
    -fkClientID foreign key to tblClient
    -dteAssessment (date of assessment)

    Now from the assessment, multiple clinical domains may apply as you describe for self care and mobility

    tblClientClinicalDomains (Client X would have 2 records in this table, 1 for self care and 1 for mobility)
    -pkClientCDID primary key, autonumber
    -fkClientAssessID foreign key to tblClientAssessments
    -fkClinicalDomainID foreign key to tblClinicalDomains

    Now for self care for client X you have 2 issues (so a one Clinical domain to many issues): Unable to achieve independently &Equipment requ


    tblClientCDIssues (client X will have 3 records in this table, 2 related to the self care and 1 related to mobility)
    -pkClientCDIssueID primary key, autonumber
    -fkClientCDID foreign key to tblClientClinicalDomains
    -fkIssueID foreign key to tblIssues

    Now, you say that an issue can have many causes/indicators/actions/appendices (Along with Issue and as demonstrated below Cause, Clinical Indicators, Actions/Considerations and Appendix can have multiple values (reasons) selected for client X having Decreased Mobility. )

    Since each of the categories (cause, indicators etc.) can have multiple values relative to the issue, each has a one-to-many relationship with the issue, so 4 separate but related tables

    tblClientCDIssuesCauses
    -pkClientCDIssCauseID primary key, autonumber
    -fkClientCDIssueID foreign key to tblClientCDIssues
    -fkCauseID foreign key to tblCause or if you'd rather you can just have a free-from text field, but if you have a cause that may apply to many issues then having a table to hold all possible causes is probably best)

    tblClientCDIssuesIndicators
    -pkClientCDIssIndicatorID primary key, autonumber
    -fkClientCDIssueID foreign key to tblClientCDIssues
    -fkClinicalIndicatorID foreign key to tblClinicalIndicators (again, you could just use a free-form text field, but if you have a clinical indicator that may apply to many issues then referencing a table that holds them is probably best)

    You would use the same basic structure for tables linking Actions/considerations and appendices back to the issue.


  5. #20
    Duncan is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Oct 2010
    Posts
    71
    Thanks of the suggestion of an assessment date. I did not consider that in my original design. Also thanks again B/C I can now see the requirement for Many to Many relationships with ‘linking tables’ and the importance of getting the fundamental relationships right from the start. At the risk of appearing stupid, what tables would you consider to be the input tables? I would assume the following in a Form;

    TblClient = For the client details
    TblClientAssessments = Assessment date
    TblClientClinicalDomainsID = For associated Clinical Domain
    From here a bit hazy, but,
    TblIssue = for the issues (as you suggested this table will hold all preset issue values for all CDs)
    TblCause = for the Cause (as you suggested this table will hold all preset cause values for all CDs)
    TblClinIndicators = for the Clinical Indicators (as you suggested this table will hold all preset Clinical Indicator values for all CDs)

    The reason why I’m clarifying is because I’m mind-full of the computer skill level of the end users (staff using the Forms). I need to make it idiot proof with no use of numbers and freetxt fields. So in my mind that relates to words not ID numbers and all data in multiselect predetermind list boxs. For example, I would use for ClinicalDomainID the actual Clinical Domain name (Self Care, Mobility etc) instead of an associated number in the table tblClinicalDomainID foreign key to tblClinicalDomains (pk the Clinical Domain name not number). This means the staff will be selecting a name not a number.

    ? is that acceptable or even right in such a data base. It seems to work on my trials because there is no actual data input in tblClinicalDomains only relationships providing the Clinical Domain names to other tables.

    Attached the table and relationship representation of your last post. Would you use an append Qry or other to get all the info into the one table (client details, CD heading with the issue, cause, clinical indicators etc details)

  6. #21
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    what tables would you consider to be the input tables?
    I'm not sure what you mean, all tables will need to have data put into them.

    You had the CDID/domain name in the issues, clinical indicator and cause tables. That is incorrect, those tables should just have the issues, clinical indicators and causes nothing else. You would relate the issues, indicators and causes to the client via the junction tables.

    I would use for ClinicalDomainID the actual Clinical Domain name (Self Care, Mobility etc) instead of an associated number in the table tblClinicalDomainID foreign key to tblClinicalDomains (pk the Clinical Domain name not number). This means the staff will be selecting a name not a number.
    All joins are made with the number fields and that is what gets stored in the tables. You only have the applicable names in their own tables. Your users should never see the tables, so they should never see the numbers. You would use combo boxes that show the names to them in a form.

    As a side note, it is not recommended to have spaces or special characters (#,/,? etc.) in your table or field names.

    I cleanup and finished the structure in the attached and created a basic data entry form for adding clients, their assessments and associated clinical domains as an example.

  7. #22
    Duncan is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Oct 2010
    Posts
    71
    The sytem looks great thanks. Two things.
    1. You had the CDID/domain name in the issues, clinical indicator and cause tables.
    Re: above. The reason why I thought that might be needed is that the end user interface tables (tblissues, tblclinicalindicators, tblcause etc) though forms will have redundant information not required for the end user to select from and I thought CDID/domain name in the table might be a way to separate out the data into their associated CD’s. That is each CD has specific Issues, Causes etc relating to it that does not relate to any other CD.

    So what I mean is that when referring to Client X with Self Care and Mobility problems. When the staff is selecting Self Care they do not need to see issues, Causes, Clinical Indicators etc, associated with Mobility, only data referring to Self Care. As it stands if I use cboboxs or lists in a form with tblIssue say, the user will see issues relating to both Self Care and Mobility. They do not need to see (they will get confused) information relating to Mobility issue as it is unrelated to Self Care. – I may have missed something re: this.

    2. You would use combo boxes that show the names to them in a form.
    Re: above. This takes me back to one of my original issues. Getting multiple selected values into the one table cell. In reviewing past posts (Ref: your post No. 11.) you have mentioned multi-select list boxs as your preference at the form level. So re: getting multiple values for Cause, Clinical Indicators etc into the one table cell is list box with Other tab = Multi Select set to Simple or Extended the way to go. I must admit I have played with this option and the data does not load into the output tables. I think this is a common problem for newbies as it is a popular post issue. Suggestions on the forum are queries and/or VBA coding to solve this. You mentioned an append Query.

  8. #23
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    . The reason why I thought that might be needed is that the end user interface tables (tblissues, tblclinicalindicators, tblcause etc) though forms will have redundant information not required for the end user to select from and I thought CDID/domain name in the table might be a way to separate out the data into their associated CD’s. That is each CD has specific Issues, Causes etc relating to it that does not relate to any other CD.
    Hiding the issue on a form is one thing (but hiding it might confuse your users), but you said in an the attachment you posted earlier that it was the issue to which the causes, indicators etc. were tied and not directly to the CD.

    The reason why I thought that might be needed is that the end user interface tables (tblissues, tblclinicalindicators, tblcause etc) though forms will have redundant information not required for the end user to select from and I thought CDID/domain name in the table might be a way to separate out the data into their associated CD’s. That is each CD has specific Issues, Causes etc relating to it that does not relate to any other CD.
    Correct there are other ways to add records. I just wanted to show a somewhat basic way to start.

    Because of your first response above, before we go any further, you have to make the call whether the relationships (issue tied to CD and all other factors tied to the issue) we currently have are consistent with the process you are trying to model or whether each issue and the other factors should each be tied to the CD. Since you know the details of your process, only you can make that call.

  9. #24
    Duncan is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Oct 2010
    Posts
    71
    Thanks, Yes I understand you point, I probably explained , my idea a little hazily. Originally I thought the CD name would be the primary and issue, cause etc tied to that. However, on your suggested modelling within each CD heading there is an equally important issue of which Causes etc tie to. And apart from CD (which is just grouping the data) the issues within are the real diagnostic problem needing attention. So when considering both it would be what is best re: database rationalisation, database design, form design for user interface, ease of querying data and in the end getting the data into a manageable format (ideally in my mind all in the one table/query). I would prefer the current modelling if pos.
    Find attached a trial form I have developed. Data selection up top and data sheet of already selected CD’s and details down bottom. The reason for this is that past CD’s and their details need to be able to be updated as the client progresses if previously selected. Client X moves from a wheelchair to walking for mobility. This will not require a whole new record just updating an existing one.
    So where to from here? Re: data manipulation etc. I have a few ideas.

  10. #25
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    So where to from here? Re: data manipulation etc. I have a few ideas.
    The next step is setting up forms. You can have forms strictly for data entry and other forms for viewing/editing records or you can have forms used for both data entry as well as viewing/editing. I don't know if you can mimic the exact form you posted. With the structure of the database, you will need forms with subforms. The main form is usually based on the table that makes up the one side of the one-to-many relationship and the subform will be based on the table that makes up the many side of the relationship.

    I know we discussed making selections from a multiselect listbox and appending those selections. I have an example database that I have used but I don't have a copy at home, so I will provide that tomorrow when I get to work.

  11. #26
    Duncan is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Oct 2010
    Posts
    71
    Many thanks for your continued support and help.

  12. #27
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Actually, I had some time to put together a form that illustrates the use of a multiselect list box. The database is attached.

  13. #28
    Duncan is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Oct 2010
    Posts
    71
    Thank you for the form with the code. Especially with the comments. When reviewing it with the comments, I can make some sense of the code (obviously I actually can’t write code though). You mentioned form design is the next step and what is achievable and what’s not. So with this in mind I’ve tried do some form engineering by myself. The following is the issues as I see it and how I tried to circumvent them now that the tables are rationalised.
    1. Extracting data from the Issues, Causes etc tables that related specifically to the Clinical Domains when an associated Clinical Domain is selected.
    2. Able to get multi-selected data into one table ‘cell’ and in the same order as in the original list.
    3. Able to review this data and change it after.
    4. Have the data represented in the receiving table or query in the same order as the source tables (issue, cause etc)
    Using AfterUpDate Code (got from MS Access website example ‘How to Synchronize Combo Boxs’) when a Clinical Domain is selected in a list box the corresponding issue, cause etc data is loaded into a list or combo box (code below).
    Pivate Sub lstClinDomain_AfterUpdate()
    Me.cboIssue.RowSource = "SELECT Issue FROM" & _
    " Issue WHERE CDID = " & Me.lstClinDomain & _
    " ORDER BY CDID"
    Me.cboCause.RowSource = "SELECT Cause FROM" & _
    " Cause WHERE CDID = " & Me.lstClinDomain & _
    " ORDER BY CDID"
    End Sub
    Obviously I had to put in the issue, cause etc tables the CDID which we took out on earlier posts to link back to the Clinical Domain table. This even works to some degree with multi-select combo boxs. The receiving table is fine however when using navigation butting to go back through the records on the form again data from other CD issues, causes etc are loaded in. So doing it this way I can with varying success achieve what I want the end user to be able to do. However it is using the undesirable mutliselect combobox instead of you example of coded multiselect list box. So again where to from here? I wondering if I could stumble through your example code and adapt it to the issues, cause etc list boxs?

  14. #29
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    ...when a Clinical Domain is selected in a list box the corresponding issue, cause etc data is loaded into a list or combo box
    This goes back to our previous discussions about whether the clinical domain is directly related to the issues and whether it is the issues that are directly related to the other items (actions/considerations, appendices etc.) OR whether each item type (issues, actions/considerations etc.) is directly related to the clinical domain. You have gone back and forth on this. This points out how important it is to get the table structure and relationships sorted out first since no amount of code or creative form designing will fix a flawed table structure.

    So in response to your question: "So again where to from here?" it depends on what table structure is right for your application/process. So, you will have to make the decision on what is the right table structure. Once you have that, I would be willing to help further.

  15. #30
    Duncan is offline Advanced Beginner
    Windows Vista Access 2007
    Join Date
    Oct 2010
    Posts
    71
    Again sorry for the changing thought process on my behalf. Some background, this is a small aspect of a larger database I’m trying to develop in my own time as a tool to educate junior nursing staff and support decision making for more senior nursing staff on my local brain injury rehabilitation ward. I have been able to do the rest of the database as it was relatively simple and striate forward educating myself in Access along the way. So back to your question.
    It will not matter either way to the consumer if CD is linked directly to issue, cause etc or if the CD is linked to issue which is then linked to cause etc (they will not know the difference). Personally I see the value of linking a CD to issue and then so forth however this is one more step in coding and database design and development even though the tables are already set up for this and again for the extra assistance it may create from your end I do not think the consumers will see the value in it. Originally I thought a CD would be selected then issue, cause etc would be made available to select (the trial Form I attached was designed to represent this). As you are providing the help if it is easier to have a CD linked to issue, cause etc instead of the other way and a Form can be developed like the one I sent then could we please do it that way, I’m assuming this will also be the easer way out of the two.

Page 2 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