From the PM......
I've looked at the relationships and am confused. It looks like you have added (at least) 3 junction tables, plus 2 other tables.
The main problem is probably the linking. I marked up a picture of the relationships...
The main question was about getting a FK in a query. The 3 tables are using the wrong linking fields.
Exams.
ExamID_PK should be linking to URINE_EXAMS.
EXAMLINK and BLOODCOUNT_EXAMS.
EXAMLINK,
NOT Exams.
Exam linked to URINE_EXAMS.
EXAMLINK and BLOODCOUNT_EXAMS.
EXAMLINK.
(I see this linking error quite often)
Not sure those tables should be linked....I don't understand the data enough to see the relationships.
Also I noticed you have added quite a few look up fields in some tables. Not a good idea... see
http://access.mvps.org/access/lookupfields.htm
Another thing I was looking at .... it looks to me that the ANALYSER (sic) fields are in too many tables. If you order an exam, do you specify the analyzer or is the analyzer part of the test results??
You have added tables for BloodCount and Urine tests. What other/how many tests/exams are there? Are you going to add a results table (junction) for each of the other tests/exams?
From the EXAMS table, it looks like there are 54 total tests (so far)........