One to Many relationship

[ramindya]

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

[June7]

That increment behavior is correct by design. The identity field type generates a unique record ID for the table and cannot be duplicated. The purpose of identity field is same as Autonumber in Access. It is intended to be a unique PK, not used as 'real world' data. Also, like Access Autonumber, I expect gaps in the sequence can happen. I don't use SQL but I doubt what you want can be accomplished without VBA code that saves value into a text or number field. Why is it a concern to have this sequence by patient saved to table? This value can be produced in a query when needed. It is a tricky query but is possible.

[ramindya]

Hello:

I have attached the sample working database which provides the expected result but ...

It works perfect if the database back end is only MS Access.My backend is SQL Server ( I have imported it to MS ACCESS). I tried creating tables similar to that sample in SQL Server and linked those tables in the MS ACCESS 2010.

Issue: After linking It does not allow me to have the "Yes ( Duplicates OK) for the MRN ( Foreign Key ) in MS ACCESS indexed property.
As a result it is not allowing me to create new rows in the sub form for next visit.

PS: I have set the ID field as identity in SQL SERVER and incremented by 1

Please let me know how to resolve this...

[June7]

I downloaded the db. The file size of the mdb is 0 and Access does not recognize the file as valid. Somehow did not zip correctly.

[ramindya]

Finally it works:

PLease refer this thread:
http://www.dbforums.com/microsoft-ac...ml#post6537838