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

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:
In Access (VBA):
On the server, this can even be a computed column.

Look at "DemoPatientVisitsA2000.mdb" (attachment, zip).
Look at Tables, Forms, (VBA).
Open "frmPatient" and try.

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.

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

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