Thank you, Ciro - you are very welcome! Such flattery deserves a response
:-)
I was thinking, if you didn't have data for every day, but still wanted to
group by blocks of five days, then you could use:
GROUP BY ([DateField]-DMin('[DateField]','[YourTable]')) \ 5
This would be faster, as the DMin needs to be calculated only once, but if
the record was missing for, say, 8 March, then the second row would average
only four records, but still have dates from 6 Mar to 10 Mar, so the average
would drop to 11.75:
StartDate EndDate Average
01/03/2013 05/03/2013 9.4
06/03/2013 10/03/2013 11.75
31/03/2013 04/04/2013 15.4
Take your pick :-)
Best wishes,
Graham
> From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of cc_electronic
> Sent: Wednesday, 10 April 2013 20:56
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: Please help: avg every 5 contiguous
records...
>
>
> @Graham, You are a phenomenon!!!
> Works fine! :-D :-D
> Thanks a lot!
>
> Ciro
>
> --- In MS_Access_Professionals@yahoogroups.com, "Graham Mandeno"
<graham@...> wrote:
> >
> > Hi Ciro
> >
> > Try this query:
> >
> > SELECT
> > Min([DateField]) AS StartDate,
> > Max([DateField]) AS EndDate,
> > Avg([ValueField]) AS Average
> > FROM [YourTable]
> > GROUP BY
> > DCount('*','[YourTable]','CLng([DateField])<' & CLng([DateField]))\5;
> >
> > Of course, you will need to substitute your own table name and field
names.
> >
> > It works by calculating a "Partition" value by counting the number of
> > records with a date less than the current record and performing an
integer
> > division by 5. So the first five records have a value of 0, the next
five
> > have a value of 1, etc. It then uses this partition value to group an
> > aggregate query.
> >
> > A subquery would be more efficient for calculating the partition, but
> > unfortunately Access will not allow a subquery in a GROUP By clause, so
we
> > must use DCount instead.
> >
> > Best wishes,
> > Graham [Access MVP 1996-2013]
> Visit Your Group
>
> Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use • Send us
Feedback
> .
>
>
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (7) |
Tidak ada komentar:
Posting Komentar