ACCESS FEED: Re: [MS_AccessPros] Query to show all records.

Sabtu, 22 Maret 2014

Re: [MS_AccessPros] Query to show all records.

John,

I made separate queries and it works but I would appreciate the
knowledge needed to do it your way.

Cheers,

Robin

At 23/03/2014 09:36 AM, you wrote:
>Robin-
>
>Whenever you add a WHERE criteria to a table involved in an outer
>join, you negate the outer part. Try creating a query like this:
>
>SELECT tClubPostHolders.ClubID
>FROM (tClubPosts RIGHT JOIN ((tRotaryYear RIGHT
>JOIN tClubPostHolders ON tRotaryYear.YearID =
>tClubPostHolders.YearID) LEFT JOIN Members ON
>tClubPostHolders.MemberID = Members.MemberID) ON
>tClubPosts.tblClubPostID = tClubPostHolders.ClubPostID) ON
>ClubsActive.ClubID = tClubPostHolders.ClubID
>WHERE (((tRotaryYear.YearStart)>"30/06/" &
>Year(DateAdd("m",+6,Date())) And (tRotaryYear.YearStart)<="30/06/" &
>Year(DateAdd("m",+18,Date()))))
>
>Then LEFT JOIN that with ClubsActive.
>
>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)
>
>
>
>
>On Mar 22, 2014, at 11:01 PM, Robin Chapple <robinski@mymail.net.au> wrote:
>
> > I have a table: "ClubsActive" which is part of a query. I need every
> > club to be listed even if the club shows no records.
> > I am using this SQL:
> >
> > SELECT ClubsActive.ClubName, tClubPostHolders.ClubID,
> > tClubPosts.tblClubPostID, Members.Directory, Members.AddressFull,
> Members.Email
> > FROM ClubsActive LEFT JOIN (tClubPosts RIGHT JOIN ((tRotaryYear RIGHT
> > JOIN tClubPostHolders ON tRotaryYear.YearID =
> > tClubPostHolders.YearID) LEFT JOIN Members ON
> > tClubPostHolders.MemberID = Members.MemberID) ON
> > tClubPosts.tblClubPostID = tClubPostHolders.ClubPostID) ON
> > ClubsActive.ClubID = tClubPostHolders.ClubID
> > WHERE (((tRotaryYear.YearStart)>"30/06/" &
> > Year(DateAdd("m",+6,Date())) And (tRotaryYear.YearStart)<="30/06/" &
> > Year(DateAdd("m",+18,Date()))))
> > ORDER BY ClubsActive.ClubName;
> >
> > Clubs without entries in "tClubPostHolders" do not show.
> >
> > What can I do to rectify the problem?
> >
> > Many thanks,
> >
> > Robin Chapple
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo Groups Links
> >
> >
> >
> >
>
>
>
>------------------------------------
>
>Yahoo Groups Links
>
>
>