1 Reply Latest reply on May 1, 2011 9:36 AM by LaRetta_1

    Unique ID for serial visits



      Unique ID for serial visits


      I have created a database where a visitor fills out a questionnaire.  This person is assigned a unique number with "auto enter serial number."  At the follow up visit, the same person fills out the same questionnaire. I want to chart their progress  How do you cluster the same person's visit records without creating a new unique identifier?

        • 1. Re: Unique ID for serial visits

          I do not know how you have this structured, James, but it sounds like you are using only one table.   You should have three tables:  Visitors, Questions and Answers, each with their own auto-enter unique ID as VisitorID, QuestionID and AnswerID.  The Answers table can hold two answers for the same question (the first time a visitor answers and then the second).  It will hold all the answers to all of the questions for all of the visitors (and is akin to a LineItems table on an invoice).

          The Answers table will be the 'many' side to Questions with the following fields:

          AnswersID (auto-enter uniqueID)
          VisitorID (ID of person providing this answer)
          QuestionID (the question this answer responds to)
          AnswerDate (auto-enter creation date so you can view answers in sequence by same visitor to same questions).

          If you would like assistance on how these would relate for the User interfaces, we could provide an example as well.