ACCESS FEED: Re: [MS_AccessPros] Missing records

Kamis, 25 Oktober 2012

Re: [MS_AccessPros] Missing records

Thanks Duane and John,

The fault was my design. I had assumed that 'Honorary' members would
be ex members but that is not the case and the SELECT only finds ex
members. Back to the drawing board.

Robin Chapple

At 25/10/2012 06:21 PM, you wrote:
>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
>
>
>
>
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>