ACCESS FEED: [MS_AccessPros] Missing records

Rabu, 24 Oktober 2012

[MS_AccessPros] Missing records

I use a query to find the current club of members.

The query 'qMembers' has all members, the query
'qMembersClubNameHistory' has the historical records of the clubs of
which each member has held membership.

With the query below not all of the members are shown in the
resulting dataset. The 'MemberID' is in both queries.

Where do I look for factors that could prevent a record showing?

Many thanks,

Robin Chapple

SELECT qMembers.MemberID, qMembers.LastUpdate, qMembers.FirstName,
qMembers.BadgeName, qMembers.LastName, qMembers.StreetAddress,
qMembers.Suburb, qMembers.Postcode, qMembers.PhoneAH,
qMembers.PhoneBH, qMembers.Fax, qMembers.Mobile,
qMembers.PartnerFirstName, qMembers.PartnerSurName,
qMembers.PartnerBadgeName, qMembers.Email, qMembers.Email2,
qMembers.Email3, qMembers.Classification, qMembers.MainActivity,
qMembers.SubActivity, qMembers.PHF, qMembers.MPHF, qMembers.PHS,
qMembers.DateJoinedPHS, qMembers.PHSCharterMember, qMembers.EREY,
qMembers.RIID, qMembers.Gender, qMembers.Notes, qMembers.Password,
qMembers.UserName, qMembers.DateJoinedRotary, qMembers.Salutation,
qMembers.Award, qMembers.Status, qMembers.UserLevel, qMembers.DOB,
qMembers.PartnerDOB, qMembers.Anniversary, qMembers.LanguageSkills,
qMembers.OccupationalSkills, qMembers.SpPhone, qMembers.SpPhone2,
qMembers.Committees, qMembers.FullName, qMembers.NameFull,
qMembers.NameCommaFull, qMembers.Directory, qMembers.AddressFull,
qMembers.State, qMembers.Memberz, qMembers.IncEmail,
qMembers.Recognition, qMembers.PartnerInc, qMembers.IncPartner,
qMembers.LastUpdated, qMembers.EmailFull, qMembers.StatusTxt,
qMembers.Phones, qMembers.Exclude, qMembers.ExcludeD,
qMembersClubNameHistory.DateJoinedThisClub,
qMembersClubNameHistory.ClubID, qMembersClubNameHistory.ClubName
FROM qMembers LEFT JOIN qMembersClubNameHistory ON qMembers.MemberID
= qMembersClubNameHistory.MemberID
WHERE (((qMembers.Status)=1 Or (qMembers.Status)=7) AND
((qMembersClubNameHistory.ClubName) Is Not Null) AND
((qMembersClubNameHistory.DateTerminated) Is Null)) OR
(((qMembers.Status)=2) AND ((qMembersClubNameHistory.ClubName) Is Not
Null) AND ((qMembersClubNameHistory.DateTerminated) In (SELECT
Max([DateTerminated]) FROM tMembersClubHistory temp WHERE MemberID =
tMembersClubHistory.MemberID )));