RE: [MS_AccessPros] Missing records with 'nil returns'.

The LEFT JOIN is negated since you have criteria against the qYearStart.YearStart field.
You might want to try:
SELECT qAttThisMonth.ClubID, qYearStart.ClubName, qYearStart.Members
AS [Year Start], qAttThisMonth.Members,
[qAttThisMonth!Members]-[qYearStart!Members] AS Change,
qAttThisMonth.MeetingsHeld, qAttThisMonth.MonthPerCent,
qAttThisMonth.ReportMonth, qYearStart.YearStart
FROM qYearStart
LEFT JOIN qAttThisMonth ON qYearStart.ClubID = qAttThisMonth.ClubID
WHERE qAttThisMonth.ReportMonth="july 2011"
AND
(
qYearStart.YearStart=IIf(Month(Date())>=7,
DateSerial(Year(Date()),7,1),
DateSerial(Year(Date())-1,7,1))
OR qYearStart.YearStart Is Null
)
ORDER BY qYearStart.ClubName;

Duane Hookom
MS Access MVP

To: MS_Access_Professionals@yahoogroups.com
From: robinski@mymail.net.au
Date: Wed, 31 Aug 2011 09:30:18 +1000
Subject: [MS_AccessPros] Missing records with 'nil returns'.

My data is housed on a remote server and is access by password
protected ASP pages.

I need to generate a monthly report on line for club attendance. The
query "qYearStart" has 61 entries.

'The query "qAttTheMonth" has less than 61 entries every month
because clubs are recalcitrant entering their reports. I have this
SQL which is supposed to give me 61 records with blanks for the 'No Returns'.

This SQL gives me the 40 records for clubs who have reported but
ignores the 'No Returns'

SELECT qAttThisMonth.ClubID, qYearStart.ClubName, qYearStart.Members
AS [Year Start], qAttThisMonth.Members,
[qAttThisMonth!Members]-[qYearStart!Members] AS Change,
qAttThisMonth.MeetingsHeld, qAttThisMonth.MonthPerCent,
qAttThisMonth.ReportMonth, qYearStart.YearStart
FROM qYearStart LEFT JOIN qAttThisMonth ON qYearStart.ClubID =
qAttThisMonth.ClubID
WHERE (((qAttThisMonth.ReportMonth)="july 2011") AND
((qYearStart.YearStart)=IIf(Month(Date())>=7,DateSerial(Year(Date()),7,1),DateSerial(Year(Date())-1,7,1))))
ORDER BY qYearStart.ClubName;

How do Include the other clubs?

Many thanks,

Robin Chapple

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

------------------------------------

Yahoo! Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/MS_Access_Professionals/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/MS_Access_Professionals/join
(Yahoo! ID required)

<*> To change settings via email:
MS_Access_Professionals-digest@yahoogroups.com
MS_Access_Professionals-fullfeatured@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
MS_Access_Professionals-unsubscribe@yahoogroups.com

<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/