For simplicity's sake, let's say I have a club membership database with 2 tables, tblMember and tblOfficer. tblMember contains the fields memID (key), memActive, memFirstName, and memLastName. tblOfficer contains the fields memID, ofcOffice, and ofcYear. They are joined by memID.
My problem is creating a query which will include in its dynaset all active members and show their membership number and name for all and their office if they are currently holding office.
When I create the query using only the tblMember table with the memActive criteria of '=Yes', I get the desired results of all active members. But when I modify the query to add the tblOfficer table without specifying any criteria for the field ofcOffice, the dynaset only shows the active members who have ever held an office. It does not show active members who have never held an office. Why? I suspect it has to do with the tblOfficer table only containing the membership numbers of current or past officers and the conditions of 'active members' and 'all officers'.
How do I structure my query to produce a dynaset of all active members, whether or not they have held office, and their office if they are currently serving?
Chuck