Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2012
    Posts
    21

    Unanswered: One to Many Relation ( Sql Server Back end )

    FRONT END INFORMATION:

    My front end of Patient & Visits windows application is done with MS ACCESS 2010.

    It has two forms and there are many other informations fields which I havent mentioned for the simplicity.

    MRN information goes to the main form and the visit information goes to the Visits subform. The Visit number automatically populates ( Auto number ) in the Visit_Number field with the visit number as soon as user saves the record.

    There is right arrow to move to the next record both in the main form and also sub form depending on visit for the same patient or user is entering information for new patient record.

    Visit number is a automatically populated and incremented and user is
    not entering it.

    BACK END/DATABASE DESIGN AND ISSUE/QUESTION:

    Patient table has following fields:
    MRN which is primary key, Patient_Name

    Visit Table has following fields:
    Visit_Number, MRN ( Foreign Key)

    Information:
    User of application is the interviewer and enters patient information for each visit as a record/row.
    John is patient 1 and made 2 visits.
    Andrew is patient 2 and made 3 visits.

    Visit_Number should increase from 1 for each unique patients.
    Expected correct result:
    MRN NAME Visit_Number
    ----------------------
    123 John 1
    123 John 2
    456 Andrew 1
    456 Andrew 2
    456 Andrew 3

    Right now in SQL server after creating one to many relationship between pateint(MRN)and visit table(MRN)and setting Visit_Number as an identity with an increment of 1.My current output (wrong) is:
    MRN NAME Visit_Number
    ----------------------
    123 John 1
    123 John 2
    456 Andrew 3
    456 Andrew 4
    456 Andrew 5

    Can you please let me know how to get the expected result ? Thanks in advance !!

  2. #2
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    IDENTITY is doing exactly what it is supposed to do - providing a unique "identity" number for each record you insert. you cannot use IDENTITY to do what you want (...directly but see the "riskier" route discussed below).

    how do you measure "first visit", "second visit" etc?
    one possible way is to have a VisitDT datetime field ..."first" is then John's earliest VisitDT, and so on.

    so to generate your visit_number 'on the fly':
    Code:
    SELECT  MRN
          , VisitDT
          , ROW_NUMBER() OVER(PARTITION BY MRN ORDER BY VisitDT) AS Visit_Number
    FROM    dbo.Patients;
    no need to store this since you can generate it whenever you want it.
    ...SS2005 or higher!!

    another riskier way is to have an IDENTITY field and to assume that the lowest IDENTITY value is the first visit ...i say "riskier" because it doesn't handle the oooops cases: forgotten first visit entered after the second visit; re-INSERT of a DELETEd record etc.
    simply change to: ....PARTITION BY MRN ORDER BY YourIdentityField....

    izy
    Last edited by izyrider; 02-12-12 at 12:12.
    currently using SS 2008R2

  3. #3
    Join Date
    Feb 2012
    Posts
    21

    Solution refer this thread

    Skip this thread.
    Last edited by ramindya; 02-12-12 at 12:35.

  4. #4
    Join Date
    Feb 2012
    Posts
    21

    solution


Posting Permissions

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