Need help relating tables

[LeahJB]

Hi, I am very new to Acccess and programming language. Please, if you answer this question, use as basic, common language as possible. Here it goes:
I have three sets of data I'm trying to relate. There is the "Family" table, "Individual" table and "Assessment" table. There can only be one family for each Family ID, but there can be multiple indivduals in the family. There can only be one indivudal per family role/ID (i.e., "mother," "target child," etc.). There can be multiple assessments for each individual.
Therefore, the family table key is "Family ID" and the key for individuals is a composite of Family_ID+Invidiual_ID (the family Id value is taken from the family table). The Key for the assessment is a composite of Family_ID+Invidiual_ID+AssessmentPt. (the family Id and the Individual ID values are taken from the family table and individual table respectively).



I can get the Family and individual tables to relate appropriately, but not the assessment table. The most common problem is that the relationships between the Individual and Assessment tables are listed as "indeterminate" and so I cannot create referrential integrity.

Basically, I am creating a composite key that I then use as a foreign key, which I think might be the issue, but logically I am having a difficult time understanding why this won't work.

Thank you!

[June7]

Are you assessing families or individuals?

Review this tutorial http://forums.aspfree.com/microsoft-...es-208217.html

[LeahJB]

Hi June, I am assessing individuals, though they are grouped into families, which are also units of analysis sometimes. I think that the central unit of analysis is individuals, though.

thank you!
Leah

[orange]

I have looked at your general info and have created a diagram that may suit your needs.
I have included fields such as otherInfo which simply represents other field(s) that you may need that are specific to this entity. I don't know the details you are keeping/needing, but have included Primary Keys, Foreign keys and unique index based on my reading of your needs.
Good luck. I hope it's helpful. Questions are welcome.

[LeahJB]

Thank you for your help, but I was alerted by someone else that I could resolve my relationship problem by assigning multiple relationships at one time between two tables (the individual and assessment tables) in the relationship dialogue box, therebny maintaining the natural keys, both composite and foreign, etc.
It's a basic thing that one can do in Access, but that I did not know how to do and that for some reason does not show up in the access training materials generally available. Since this is my initial forray into Access, I'm sure I will have more quesitons in the future.
Thank you for your attention to this.
Leah