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 !!

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':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

Skip this thread.

Please refer the following link:

One to many relationship (Pateint & Visits)