MS Access Many-to-Many Query/Form Assistance

many-to-manyms access

I have somewhat of a beginner-type question.

Could someone look at the below relationships and let me know why I can't get the following form/query to only show the unique Appointment letters? I have 143 appointment letters. I have 135 members. I have 585 records in the junction table. I know that I messed up the form source query (below), but how badly am I off? Your expertise is greatly appreciated.

Resources used:

Screenshot of Relationships:
Screenshot of Relationships

Form:
Screenshot of Form

Query:
Screenshot of Query

SQL:

SELECT DISTINCTROW tbl_apptltrs.*, jtbl_apptltrs_mbrs.*, tbl_mbrs.*
FROM tbl_mbrs INNER JOIN (
    tbl_apptltrs INNER JOIN jtbl_apptltrs_mbrs
    ON tbl_apptltrs.ApptLtrsID = jtbl_apptltrs_mbrs.LinkedApptLettersID)
ON tbl_mbrs.MembersID = jtbl_apptltrs_mbrs.LinkedMembersID;

Best Answer

I'm having to do a little bit of psychic debugging here, but generally speaking, you should have the subform bound to a query that joins your junction table to the table of related records. The form inside the subform shouldn't know about the table in the left side of the relationship (tbl_apptltrs).

So the chain is something like this:

  1. Main form bound to tbl_apptltrs.
  2. Subform linked to main form via primary key of tbl_appltrs (e.g. parent form ApptLtrsID = subform LinkedApptLettersID).
  3. Form inside subform bound to query that joins jtbl_apptltrs_mbrs to tbl_mbrs (jtbl_apptltrs_mbrs.LinkedMemberID = tbl_mbrs.MemberID).
Related Question