select p.MRN,p.Patient_Name,ROW_NUMBER() OVER (PARTITION BY p.MRN ORDER BY v.Visit_Number) AS Visit_Number
from Patient p
inner join visits v
on v.MRN = p.MRN

quote:

---

Originally posted by ramindya

Forgot to mention in my previous reply.

My front end of this 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. My apologizes if I am not clear.I am new bie to MS Access and database design.

Thanks again !!

---

quote:

---

Originally posted by ramindya

Visakh:

Thanks for your reply..

Is there any other way that I can populate the visit number in the frontend without using SQL query by adding additional table or additional field etc.Very flexible with re-designing the table/database.

Thanks,
Ram

---