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

Rabu, 31 Agustus 2011

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

Graham,

They are different from the 40 records as far as they do not show the ClubID.

The 18 records do not show.

Robin

At 31/08/2011 08:04 PM, you wrote:
>Hey, we're getting closer ;-)
>
>Is there anything "interesting" about the three extra records that you see?
>By which I mean, how do they differ from the other 18 you expect to see?
>--
>
>Graham
>
>From: MS_Access_Professionals@yahoogroups.com
>[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
>Sent: Wednesday, 31 August 2011 21:53
>To: MS_Access_Professionals@yahoogroups.com
>Subject: 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%40yahoogroups.com>
> >[mailto:MS_Access_Professionals@yahoogroups.com
><mailto:MS_Access_Professionals%40yahoogroups.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
><mailto:MS_Access_Professionals%40yahoogroups.com>
> >From: robinski@mymail.net.au <mailto:robinski%40mymail.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),D
>a
> >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
> >
> >
> >
>
>
>
>
>
>[Non-text portions of this message have been removed]
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>