Results 1 to 14 of 14
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Design help (2002)

    I have a db design that I've not had to deal with before. I've found similar questions in forums but not exactly what I need.

    I'm designing this db for "Carepaths" (the progression a patient in the hospital should make each day or stage of their hospital stay).
    My tables so far are:

    tblPatient (main table)
    PatientID
    Date
    MRNum
    AcctNum

    tblCarepath
    CarepathID
    Carepath (I'll have 20-30 Carepaths - ONE per patient) (A CAREPATH can have many STAGES and a STAGE may belong to more than one CAREPATH)

    tblStage (can belong to more than one CAREPATH and more than one LEGEND)
    StageID
    StageName (I'll have 20 stages)

    tblLegend
    LegendID
    LegendName (I'll have 50 - 100 Legends)- (Can belong to more than one STAGE)

    I believe I need:
    tblCarepathStage (to make the relationship many to many)
    CatepathID
    StageID (both IDs together as a foreign key)

    AND:
    ? IS This correct?
    tbl StageLegend (to make the Stages and Legends many to many)
    StageID
    LegendID (both IDs together as a foreign key)

    The problem is, each Carepath is associated with certain Stages and each Stage is associated to certain Legends. A HipSurgery Carepath uses only 4 of the 20 Stages in that table.
    Then, of those 4 Stages associated to the Hip Carepath, there are only 6 associated Legends out of the 50 in that table.
    Those same 4 Stages and 6 Legends may be in other Carepaths as well. In the form, I only want the user to click on a dropdown, select "Hip Carepath" then only see 4 of 20 choices for Stages, then only 6 or 7 choices for legends.

    On the form, the user needs to be able to input:
    details of the patient, a Carepath, a Stage and many Legends, a Stage and many Legends, a Stage and many Legends for each record.
    So far, the way my relationships / tables are set up, I can only enter info from the the detail table on the form and many Stages (subform) I need a second subform to enter the many Legends associated to that Stage.


    I've tried making a Detail table to restrict which Stages and Legends a Carepath can use like:
    TableID CarepathID StageID
    1 1 1
    2 1 2
    3 2 2
    Do I add Legend ID in this table? Or another separate table with StageID and Legend ID?

    Because I

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Design help (2002)

    There are a couple of ways to approach this, depending on whether there will *always* be a legend associated with a stage.

    One approach would be a join table that includes the CarepathID, the StageID *and* the LegendID. You wouldn't try and restrict this at the table level, you would do it at the form level, providing filtered combobox lists for selecting items appropriate to the carepath or stage. Since you have many legends for many stages and many stages for many carepaths, you would need a many-to-many-to-many table.

    An alternative, if a stage will *always* have a legend, would be to use your current two many-to-many tables, but give each join table a unique key of its own rather than a multiple field key. That would allow you to join a specific stage and legend to Carepath by inserting the unique key of the Stage-Legend join table into the Carepath-Stage join table instead of the StageID and LegendID. In that case, there would be no direct relationship between Carepath and Stage or Carepath and Legend. The joins to Legend and Stage would both be through the Stage-Legend table.
    Charlotte

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design help (2002)

    Charlotte, thanks.
    This gives me some guidance.
    There will *always* be a Legend, I'll work on that.
    Vicky

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Design help (2002)

    I think you need two types of tables (you hint at this yourself):

    1. Static tables describing the possible combinations of CarePath and Stage, and the possible combinations of Stage and Legend. These tables are auxiliary, they don't hold data associated with patients; they are used to restrict choices. I hope that the possible Legends associated with a Stage do not depend on the CarePath; if so, you might need to redefine the concept of Stage. One set of Legends should be associated with the Stage, even if there are several CarePaths sharing that Stage.

    2. Dynamic tables describing the situation of patients, starting with tblPatient. If, as you state, there is one CarePath per patient, you can include a CarePathID field in tblPatient. Next, there would be a tblPatientStage, containing PatientID, CarePathID and StageID (composite PrimaryKey). In the subform bound to tblPatientStage, a combo box restricts the StageID's to those belonging to the value of CarePathID in tblPatient. There is also a tblPatientLegend, containing PatientID, CarePathID, StageID and LegendID (composite primary key). In the subform bound to this table, a combo box restricts the LegendID's to those for the current StageID.

  5. #5
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design help (2002)

    Thank you Hans,
    I believe this is exactly what I've been trying to sort out.
    You are correct - One set of Legends is associated with the Stage, even if there are several CarePaths sharing that Stage. They do not depend on the Carepath.

    For the Static tables, for clarification, I define what is in this table?
    It will look something like:

    tblStaticCarepathStage
    TableID (primary key) or should it be composite CarepathIDStageID?
    CarepathID | StageID
    1 1
    1 2 << I define
    1 4
    etc.
    And
    tblStageStageLegend
    TableID (primary key) or should it be composite StageIDLegendID?
    StageID | LegendID
    1 1
    1 2
    1 5

    And what is the relationship between these two tables and the others?
    __________________________________________________ ____
    Then,
    The Dynamic tables will store the data entered and look something like:
    tblDynamicPatientStage
    PatientID
    CarePathID <<< all included to make the composite key
    StageID
    and nothing else in this table?

    tblDynamicPatientLegend
    PatientID
    CarePathID <<< all a composite key
    StageID
    LegendID

    tblPatient would be one to many to these two tables?
    and how are they related to the static restriction tables or other tables?

    Once I'm clear on that, I think I'll be fine.
    I really appreciate this, I've been stumbling on this for many days!
    Vicky

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Design help (2002)

    For the static tables, I would use a composite primary key (on CarepathID and StageID in tblStaticCarepathStage, and on StageID and LegendID on tblStaticStageLegend (I assume that is what you intented to write.) As Charlotte indicates, it would also be possible to use separate ID fields, but in the setup I proposed, I prefer to use composite PK's.

    tblStaticCarepathStage would be related to tblCarePath on CarePathID and to tblStage on StageID (with relational integrity enforced)
    tblStaticStageLegend would be related to tblStage in StageID and to tblLegend on LegendID (with relational integrity enforced)
    Since the legends for a stage are not dependent on the carepath, there is no relationship between the two tblStatic... tables.

    For the dynamic tables, I would also use a composite PK. these tables can contain other fields beside the PK fields, if you need to store information that is specific to the combination. For example, tblDynamicPatientStage will indeed have a composite PK on PatientID, CarePathID and StageID, but could also contain dates (onset date, ending date) and other fields that are relevant to the specific combination of patient and stage. Similar for tblDynamicPatientLegend.

    tblPatient would be joined to tblDynamicPatientStage on PatientID and CarePathID.
    tblDynamicPatientStage would be joined to tblDynamicPatientLegend on PatientID, CarePathID and StageID.

    tblStaticCarepathStage would be joined to tblDynamicPatientStage on CarePathID and StageID.
    tblStaticStageLegend would be joined to tblDynamicPatientLegend on StageID and LegendID.

    Dizzy now? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    It may help to sketch the relationships on a large piece of paper. Once you have created the tables and relationships, lay out the Relationships window as clear as you can, then print it.

  7. #7
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design help (2002)

    Yep, dizzy now but feeling much better about the whole thing!
    I will definately do the sketching out thing.

    I'll be doing most of the work on this from home this weekend and may have a final question or two by Mon/Tues if that's okay.
    At least now I can stand to look at it again.
    Thanks so much!
    Vicky

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Design help (2002)

    Feel free to come back and post more questions. Good luck with the design.

  9. #9
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design help (2002)

    Hans,
    I'm afraid I must still be doing something wrong. I set up the relationships like we discussed but the Form does not allow a subform for the user to enter multiple Legends.

    I just do a simple wizard Form for quick testing. The Form works fine with the tblPatient and tblDynamicPatientStage. It gives me a main Form for tblPatient and a subform for entering multiple Stages. As soon as I add the tbleDynamicPatientLegend, the form does not display. I've been trying other combinations of relationships thinking I should be able to get this straight by now. Any ideas on what might be wrong?
    Sorry to bother you once again, this has been my worst challenge with Access so far.
    Vicky

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Design help (2002)

    Perhaps the attached demo will help. It is not meant as a finished design, just as an illustration of the structure, with a data entry form.
    Attached Files Attached Files

  11. #11
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design help (2002)

    Hans, this is perfect!
    Once again I'm looking forward to working on it.
    I can't thank you enough.

  12. #12
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design help (2002)

    Hans, good morning.
    I have one final question if you don't mind.
    I have the tables set up and used your demo as a template. Everthing seems fine except I get an error when entering data in the Stages dropdown on the form.

    It says Runt-time error '438':
    Object doesn't support this property or method.

    I went back to your original demo that I have not bothered and entered a record. I got the same error.

    Could it be different versions of Access doing this?

    The de###### takes me to:
    Dim ParentDocName As String

    On Error Resume Next
    ParentDocName = Me.Parent.Name

    If Err <> 0 Then
    GoTo Form_Current_Exit
    Else
    On Error GoTo Form_Current_Err
    Me.Parent![sbfDynamicPatientLegend].Requery
    Me.StageID.Requery
    End If

    Form_Current_Exit:
    Exit Sub

    Form_Current_Err:
    MsgBox Error$
    Resume Form_Current_Exit

    End Sub

    Private Sub StageID_AfterUpdate()
    Forms!frmPatient!sbfDynamicPatientLegend.LegendID. Requery
    End Sub


    It highlights the last "Forms!frmPatient!sbfDynamicPatientLegend.LegendID .Requery" as the problem.

    I don't know enough about programming -yet but I'm working on it.
    Thanks, Vicky

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Design help (2002)

    It probably has to do with the version of the Jet Engine you're using. I didn't get the error on my home PC (Jet 4.0 SP8), but I do get it on my work PC (Jet 4.0 SP6). Try this:

    Forms!frmPatient!sbfDynamicPatientLegend!LegendID. Requery

    The only change is the use of <big>!</big> between sbfDynamicPatientLegend and LegendID.

  14. #14
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Design help (2002)

    That works fine. Everything should be great now.
    I've learned a lot from this,
    Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •