ACCESS FEED: RE: [MS_AccessPros] Join properties in a query

Sabtu, 16 Juli 2011

RE: [MS_AccessPros] Join properties in a query

Thanks John,

I've not understood the SQL. Perhaps an explanation is steps might help.

I've not met the term 'filter' applied to a query! When you advise to
apply the filter first am I right to assume that happens in the one
SQL statement?

It gives me an error which is here:

<http://www.rotary9790.org.au/test/test.asp>http://www.rotary9790.org.au/test/test.asp

Robin

At 16/07/2011 03:12 PM, you wrote:
>Robin-
>
>The instant you apply a predicate to the "right" side of a "left" join, you
>nullify the effects of the outer join. You must filter first, then apply the
>join, like this:
>
>SELECT ClubsActive.ClubName, tClubPostHolders.YearID, TCPH85.ClubID
>FROM ClubsActive LEFT JOIN
>(SELECT tClubPostHolders.ClubID, tClubPostHolders.YearID
>FROM tClubPostHolders
>WHERE (tClubPostHolders.YearID=85) AND (tClubPostHolders.ClubID=19)) As TCPH85
>ON ClubsActive.ClubID =
>TCPH85.ClubPostID;
>
>
>John Viescas, author
>Microsoft Office Access 2010 Inside Out
>Microsoft Office Access 2007 Inside Out
>Building Microsoft Access Applications
>Microsoft Office Access 2003 Inside Out
>SQL Queries for Mere Mortals
>http://www.viescas.com/
>(Paris, France)
>
>
>
>-----Original Message-----
>From: MS_Access_Professionals@yahoogroups.com
>[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
>Sent: Saturday, July 16, 2011 4:05 AM
>To: MS_Access_Professionals@yahoogroups.com
>Subject: [MS_AccessPros] Join properties in a query
>
>I have failed to understand the 'properties' in the join in a query.
>
>I have one table which I need to show all club names in the resultant
>query. The second table will supply the posts which have been filled
>and leave me with a result showing the clubs which have failed to
>make nominations.
>
>The properties show as: "Include all records from "ClubsActive".
>
>Why do I not se them in the result?
>
>SELECT ClubsActive.ClubName, tClubPostHolders.YearID, tClubPostHolders.ClubID
>FROM ClubsActive LEFT JOIN tClubPostHolders ON ClubsActive.ClubID =
>tClubPostHolders.ClubPostID
>WHERE (((tClubPostHolders.YearID)=85) AND ((tClubPostHolders.ClubID)=19));
>
>
>Many thanks,
>
>Robin Chapple
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>

[Non-text portions of this message have been removed]