ACCESS FEED: Re: [MS_AccessPros] Missing records

Kamis, 25 Oktober 2012

Re: [MS_AccessPros] Missing records

Robin-

The LEFT JOIN is useless.

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

Any time you add a filter to the "right" table of a "left" join, it's as
though you're doing an INNER JOIN. You won't get any members who have no
record in qMembersClubNameHistory. Is that perhaps the problem?

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: Duane Hookom <duanehookom@hotmail.com>
Reply-To: <MS_Access_Professionals@yahoogroups.com>
Date: Thursday, October 25, 2012 3:12 AM
To: Access Professionals Yahoo Group
<ms_access_professionals@yahoogroups.com>
Subject: RE: [MS_AccessPros] Missing records

I just remove criteria until all the records are displayed.The last
criteria removed is the culprit.

Duane Hookom
MS Access MVP

----------------------------------------
> From: robinski@mymail.net.au
>
> 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 )));

------------------------------------

Yahoo! Groups Links