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

Rabu, 31 Agustus 2011

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

Duane,

I still have this problem. Following the fact that the LEFT JOIN
should not have criteria I have made a new query "qYearStartty" which
provides the records only for 'This Year which has 61 records;.

SELECT qAttThisMonth.ClubID, qyearstartty.ClubName,
qyearstartty.Members AS [Year Start], qAttThisMonth.Members,
[qAttThisMonth!Members]-[qyearstartty!Members] AS Change,
qAttThisMonth.MeetingsHeld, qAttThisMonth.MonthPerCent,
qAttThisMonth.ReportMonth
FROM qyearstartty LEFT JOIN qAttThisMonth ON qyearstartty.ClubID =
qAttThisMonth.ClubID
WHERE (((qAttThisMonth.ReportMonth)="july 2011"))
ORDER BY qyearstartty.ClubName;

I still have only 40 records. The missing 21 are nil returns for the
current month. I need 61 records for the report.

Is there another approach?

Many thanks,

Robin

At 31/08/2011 01:38 PM, you wrote:

>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
>
>
>