ACCESS FEED: [MS_AccessPros] Missing records with 'nil returns'.

Selasa, 30 Agustus 2011

[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