MS Access Many-Many Query/Form Assistance

Question

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:

• 3 Example databases
  
• 4 MS Access 2003/2010/2013 books
  
• 5 links:
• https://stackoverflow.com/questions/13108525/ms-access-2010-query-pulls-same-records-multiple-times-sql-challenge
• https://stackoverflow.com/questions/473604/basic-many-to-many-sql-select-query
• Querying a many to many relationship in access
• Help with Access many-to-many relationship (beginner)
• Query Multiple tables in MS Access

Screenshot of Relationships:

Form:

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;

Answer 1

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).