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

Rabu, 31 Agustus 2011

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

Thanks Graham,

That gave me 43 records out of an expected 61.

Regards,

Robin

At 31/08/2011 03:36 PM, you wrote:
>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
>
>
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>