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

Sabtu, 22 Maret 2014

Re: [MS_AccessPros] Query to show all records.

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