0

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;
0

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).
  • I feel like an idiot. As soon as I performed step 1 (set Main form to have tbl_apptltrs as the Record Source) everything appears to be correct. I'll definitely clean up the subforms with more specifics (like what you did with steps 2 & 3) but I believe this fixed it. This is definitely a lot more simple than what I thought the answer was. Thank you! – kennyrogersjr Dec 9 '16 at 20:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.