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

    Unanswered: One to many relationship (Pateint & Visits)

    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
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If you set Visit_Number as an identity column with an increment of 1, the contents of this column will be incremented for every row, which should not be a problem. Keep it as you need an identity column to be able to update an attached table. It will also provide the default sort order for the table.

    You can compute (and store) the next visit rank order for each patient using:
    In SQL Server:
    Code:
    SET NextVRO = ( SELECT COUNT(*) FROM Visit WHERE MRN = ...) + 1
    In Access (VBA):
    Code:
    NextVRO = DCount("*", "Visit", "MRN = ...") + 1
    On the server, this can even be a computed column.
    Have a nice day!

  3. #3
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Look at "DemoPatientVisitsA2000.mdb" (attachment, zip).
    Look at Tables, Forms, (VBA).
    Open "frmPatient" and try.
    Attached Files Attached Files

  4. #4
    Join Date
    Apr 2005
    Location
    Zagreb - Croatia
    Posts
    372
    Hello Sinndho!
    Not "DCount" but "Dmax".
    Because "DCount" will make a mistake if the user delete 1 or more visits.
    It would be better if he put a DATE of the visit in the record.

  5. #5
    Join Date
    Feb 2012
    Posts
    21

    DemopatientVisit2A2000 works pefects but ...

    Thank You. It is working perfectly.

    I was able to replicate the same table structure/relationship in SQL server and only identity that I set was for F1ID field with an increment of one.

    Thanks MStef-ZG !! You rock !!

    Thanks,
    Ram
    Last edited by ramindya; 02-10-12 at 15:58.

  6. #6
    Join Date
    Feb 2012
    Posts
    21
    Question:
    What is the syntax of this existing code if I need to insert VisitNumber in multiple tables simultaneously ? Assume there is another table names dbo_tblScreen. Thank you in advance !!

    Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.VisitNumber = Nz(DMax("[VisitNumber]", "dbo_tblScheduling", "[MRN] =" & [Forms]![frmMainBase]![MRN]) + 1, 1)
    End Sub

Posting Permissions

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