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

Minggu, 23 Maret 2014

Re: [MS_AccessPros] Query to show all records.

Robin-

Sorry about the syntax error - I just copied and pasted without checking your parens.

When you do TableA LEFT JOIN TableB or TableB RIGHT JOIN TableA and then put criteria in the WHERE clause on TableB, the join acts like an INNER JOIN because the WHERE clause eliminates any Null values returned by B.

You did what I wanted you to do - make separate queries.

By the way, whenever you build a date filter in SQL, you must use US mm/dd/yyyy format. As in:

tRotaryYear.YearStart > "06/30/" & Year(DateAdd("m", +6, Date())

You can also do yyyy/mm/dd format, but not dd/mm/yyyy format. Your query works because the query engine is forgiving if it can see that the month value isn't valid. When it sees "30" as the month, it assumes that's the day and uses the next value as the month. But you'll get strange results if the day value is 12 or less unless you use one of the correct formats.

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 23, 2014, at 6:11 AM, Robin Chapple <robinski@mymail.net.au> wrote:

> 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
>>
>>
>>
>
>
>
> ------------------------------------
>
> Yahoo Groups Links
>
>
>
>