Relationship issues like real life

I am attempting to create relationships between my tables and am struggling mightily https://accessworld-accessworld.netd...anginghead.gif

There will be many students (student demographic tbl), some of which will have completed many programs (program tbl), all students will have completed many assessments (assessment tbl), some students will have many clinicals (clinical tbl), all students will have only one admission (admission tbl), and the tblP_Reports is a stand alone and needs no relationship.


Image of tables attached.


Thank you so much for your time!!
Doug

Doug, you need to create Many to Many Subtables, they usually contain just a few fields, a key field for that table, a foreign Key field for 1 Master table and one key field for the other Master Table are the essential fields. You can add others like a date a course was taken, or a group was joined etc.
So for your first example Students and Programs you would have
1. An Autonumber Key field.
2. A Long Type Integer Field to hold the Student's Key ID Field value.
3. A Long Type Integer Field to hold the Progam's Key ID Field value.
3. A date Field for the date they completed or joined the program.
To enter & display this data you now need Query & Form with those fields on plus 2 Combo boxes, one to select the Student and one to select the program.
If this is a subform then you can use the Master form to supply either the Student or the Program.
You can also have Subtables with more than 2 many to many relationships.
I hope this helps you visualise what is required, if required I can supply an example.

ps, this means that your other master tables do NOT have the Student ID in them.
If like the Meetings table you also would not need the date field in the subtable beacsue the Meetings Key field would not only identify the meeting it would also identify the date, unless of course it is preferable to have it in the subtable instead.