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

Rabu, 31 Agustus 2011

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

Graham,

That delivers 61 records, 3 without ClubID.

Robin

At 31/08/2011 08:07 PM, you wrote:
>Robin,
>
>
>
>As a matter of interest, how many records does this give you?
>
>
>
>SELECT * FROM qYearStart WHERE
>(
>qYearStart.YearStart=IIf(Month(Date())>=7,
>DateSerial(Year(Date()),7,1),
>DateSerial(Year(Date())-1,7,1))
>);
>
>
>
>--
>
>Graham
>
>
>
>From: MS_Access_Professionals@yahoogroups.com
>[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Graham Mandeno
>Sent: Wednesday, 31 August 2011 22:04
>To: MS_Access_Professionals@yahoogroups.com
>Subject: RE: [MS_AccessPros] Missing records with 'nil returns'.
>
>
>
>
>
>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%40yahoogroups.com>
>[mailto:MS_Access_Professionals@yahoogroups.com
><mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of Robin
>Chapple
>Sent: Wednesday, 31 August 2011 21:53
>To: MS_Access_Professionals@yahoogroups.com
><mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
> >[mailto:MS_Access_Professionals@yahoogroups.com
><mailto:MS_Access_Professionals%40yahoogroups.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>
><mailto:MS_Access_Professionals%40yahoogroups.com>
> >From: robinski@mymail.net.au <mailto:robinski%40mymail.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]
>
>
>
>
>
>[Non-text portions of this message have been removed]
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>