Query of 2 tables with "intersection" table that uses "lookup"
Posted on 2008-10-05
Objective: Store and retrieve members attendance to meetings.
MembersID [lookup into "Members" for easy selection and storage of attendance]
MeetingsID [lookup into "Meetings" for easy selection and storage of attendance]
ID in Members to MembersID in Attendance [one to many // join type: inner]
ID in Meetings to MeetingsID in Attendance [one to many // join type: inner]
Creating new records in "Attendance" works fine due to the convenience of the lookup display control - All good so far.
Now to the query that will give me the list of who attended what meeting (later I need to input the meeting I want to see - but for now first step is to get the full list to work...)
Query (it got generated by the query designer in Access 2007):
SELECT Members.FirstName, Members.SurName, Meetings.MeetingNumber
FROM Meetings INNER JOIN (Members INNER JOIN Attendance ON Members.ID = Attendance.MembersID) ON Meetings.ID = Attendance.MeetingsID;
This WORKS - but only when:
The Attendance fields MemberID and MeetingsID are NOT lookups (in "Display Control")
The moment I make the "lookup" the query stops working... However I'd like it to still work in this case - Help?!