Hi Robin
I think the check for Null needs to be on qAttThisMonth, because that's the
table on the outer side of the join:
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.ClubID is Null OR 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;
--
Graham
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Duane Hookom
Sent: Wednesday, 31 August 2011 15:39
To: Access Professionals Yahoo Group
Subject: 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),Da
teSerial(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
Tidak ada komentar:
Posting Komentar