Results 1 to 12 of 12
  1. #1
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    table relationships (2003)

    Hi all, I'm having trouble with where to place a new table with regards to it's relationship. Currently I have a tblHMO (table of doctors names,etc) with PK=HMOID. This is linked many-to-many to 3 other tables (terms, rotations, year) using a junction table (tblJunction), which represents each rotation they go through (4 each year). The new table (tblRotEval, with PK=RotEvalID) is one in which each HMO evaluates (a questionnaire) each rotation they've gone through. I am unsure where to link this table into. do i need another junction table? any advise or help is much appreciated. Regards, Van

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

    Re: table relationships (2003)

    Will the questionnaire be the same for all rotations, terms etc., or do the questions depend on the rotation or term or whatever?

  3. #3
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: table relationships (2003)

    the questions are the same irregardless of rotation

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

    Re: table relationships (2003)

    You'll need at least two new tables:

    1) tblQuestionnaire to list the questions. Fields: QuestionID (AutoNumber, primary key) and QuestionText, and perhaps ResponseType.

    <table border=1><td>QuestionID</td><td>QuestionText</td><td align=right>1</td><td>How many patients did you receive?</td><td align=right>2</td><td>How do you rate our service?</td></table>
    2) tblResponses to store the responses. Fields: HMOID, TermID, Year, RotationID, QuestionID, Response. In the example below I assumed that responses are always numbers

    <table border=1><td>HMOID</td><td>Term</td><td>Year</td><td>RotationID</td><td>QuestionID</td><td>Response</td><td align=right>1</td><td align=right>1</td><td align=right>2007</td><td align=right>1</td><td align=right>1</td><td align=right>37</td><td align=right>1</td><td align=right>1</td><td align=right>2007</td><td align=right>1</td><td align=right>2</td><td align=right>9</td></table>
    3) If you have multiple-response questions, you need an additional table listing the response categories for those questions.
    <table border=1><td>QuestionID</td><td>ResponseNo</td><td>ResponseText</td><td align=right>3</td><td align=right>1</td><td>Dog</td><td align=right>3</td><td align=right>2</td><td>Cat</td><td align=right>3</td><td align=right>3</td><td>Goldfish</td></table>

  5. #5
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: table relationships (2003)

    Hi Hans, I have attached the database as it is up to now. the new table is called tblRotEval. This is the questionnaire, but how does it link into the other tables? I don't want to have to type in the term, year, rotation, etc each time the questionnaire is completed, rather i want to choose this from a combo box.

    thanks, Van

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

    Re: table relationships (2003)

    Although it is possible to use a table design such as you used, with each question in a different field, it makes it more difficult to analyze the results, such as calculating a total score or averages.

    To link it to tblRotationHMOTermLink, you need HMOID, Term, Year and Rotation fields. You can fill in these fields using code when opening frmRotEval from anotther form where the HMOID etc. have already been chosen.

  7. #7
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: table relationships (2003)

    thanks Hans, I'll get to work on that solution. that more like what i'm after. Regards, Van

  8. #8
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: table relationships (2003)

    Hi Hans, I've been trying to figure out how to use code to fill in fields on the frmRotEval, but not sure where to look for help in past posts. I'm trying code like: stLinkCriteria = "[RotID]=" & Me![Combo2] , but I don't think i'm on the right track. Can you suggest where i might start? thanks again, Van

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

    Re: table relationships (2003)

    It depends. Do you want users to be able to go back to an existing questionnaire, or should the only be able to fill it in once for a given year, term and rotation?

  10. #10
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: table relationships (2003)

    They user should be able to go back to an existing questionnaire (and edit responses if needs be).

    Thanks, Van

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

    Re: table relationships (2003)

    When the user clicks a button to open the questionnaire, use DCount to check whether there is already a record in the results table for the selected HMOID, year, term and rotation. If not, add a record to this table with the selected HMOID etc., for example by opening a DAO recordset and using AddNew.

    Then open the questionnaire form and pass the values in the WhereCondtion argument of DoCmd.OpenForm.

  12. #12
    2 Star Lounger
    Join Date
    Oct 2006
    Location
    Melbourne, Victoria, Australia
    Posts
    129
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: table relationships (2003)

    thanks again Hans.

    Regards, Van

Posting Permissions

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