Patient Table to include Parent(s) as responsible party and a patient. 2

[monkeysee]

I have a patient table which includes names, dob, sex, etc.
Of course patients could be adults; children and dependent adults.
I want to be able to have all these people in one table; but also want to be able to identify Jane Doe as a parent of Bobby Doe. At the same time Jane Doe is also a patient.
So this is what I have. tblPatient (last name; first name, etc for fields)
I could make a table: tblParent but I don't want to store duplicate names..
That is where I am stuck with a mind block (I think I am overthinking again and can't see the nose in front of my face).
If anyone could steer me in the right direction, would greatly appreciate it.
Thanks!

 

[Andrzejek]

I want to be able to have all these people in one table"

One way to do it would be to have a structure like this:

[pre]
PK Grp Rel FName LName DOB Gender
1 1 1
2 1 2
3 1 2
4 2 1
5 2 2
6 2 3[/pre]

So your Relations (Rel) table would be something like:
[pre]
PK Who
1 Patient
2 Parent
3 Uncle[/pre]

Grp field would indicate the Group of people for that Patient (including the Patient)


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

I would create a junction table that allows for multiple adults

[pre]PatientRelationships
prePReID autonumber primary key
prePatientID from the patient table
preRelPatientID related parent or other from the patient table
preRelationID link to table Andy suggested[/pre]

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[MajP]

I do not think "Patient" would go in the relationship table. I think if all people, patients or relations go in the main table then that would be a boolean field. A person is either a patient or not. Then do as Duane describes. So if patient 1, has a mother with ID of 2, and Father with ID of 3 in the main table. And if patient 1 had a sibling 6 then it would look like this.

prePReID prePatientID preRelPatientID preRelationID
1 1 2 Mother
2 1 3 Father
3 6 2 Mother
4 6 3 Father
I doubt you would need it but you could show kid relations
5 1 6 Brother
6 6 1 Sister

Now making a form to do this may be a little tricky and you would need to come back. You have what is called a 1 to many self referencing table, and that is not done very often but it is doable.