Self populating value list in many to many relationship

[lsmall]

I am having a bit of a conceptual block on this and I can't sort it out. Some guidance would be appreciated.

I've got a patient database. Each patient has a past medical history consisting of several diagnoses. The past medical history diagnoses, ideally in a portal linked to a standardized diagnoses table, should be selected using a dropdown menu using a value list from the standardized diagnoses table. One should also be able to add a free text diagnosis to the past medical history that will be added to the value list for future use. The standardized diagnoses table should contain only unique values so that standardized diagnoses are not repeated. Of course many patients can have the same diagnosis from the standardized table.

I have tried a portal, separate fields for each past medical history diagnosis and a repeating field, but I just can't get it quite right. I think I am not conceptualizing the relationship correctly.

This is one of those things that I should just set aside, and it will probably come to me while I'm shaving or in the middle of the night, but if anyone has an idea please let me know. I'm sure there are multiple ways of doing it.

Let me know if more info or clarification is needed.

Thanks

Edited December 3, 2010 by Guest

[dansmith65]

So, it sounds like there would be 3 tables involved:

Patient

PatientDiagnoses

Diagnoses

PatientDiagnoses would need PatientID and DiagnosesID fields. If you want the portal on a Patient Layout, then it would display records from PatientDiagnoses TO, and contain a field from Diagnoses table.

If you set your relationships up to allow creation of records, I think you should be able to enter a new diagnosis into the Diagnoses table directly from the Patient layout.

Oh yeah, make your value list contain all items from the Diagnoses table.

I suspect your actual setup will be slightly more involved than this, but I think this basic concept will set you on the right track.

[lsmall]

I think you're right. I was trying to get it to work without the 3rd table. It seems obvious in hindsight.

Will give it a go.

Thanks