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]
> From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of cc_electronic
> Sent: Tuesday, 9 April 2013 07:52
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Please help: avg every 5 contiguous
records...
>
>
> Thank you Duane.
>
> OK, it's clear but I can add beside each value a corresponding date in
ascending order.
>
> Value
> 2013-03-01 5
> 2013-03-02 10
> 2013-03-03 2
> 2013-03-04 7
> 2013-03-05 23 ----> 9,4
> 2013-03-06 7
> 2013-03-07 33
> 2013-03-08 67
> 2013-03-09 6
> 2013-03-10 1 ----> 22,8
> .....
> 2013-03-31 4
> 2013-04-01 9
> 2013-04-02 11
> 2013-04-03 12
> 2013-04-04 41----> 15,4
>
> Ciro
>
> --- In MS_Access_Professionals@yahoogroups.com, Duane Hookom
<duanehookom@...> wrote:
> >
> > There is no such thing as "contiguous records" in an Access table.
Records in a table are like marbles in a box, there is no order.
> >
> > You would need to provide some field value that determines how the
records are grouped. Your other option is to use Excel which doesn't require
uniqueness or sequences.
> >
> > Duane Hookom MVP
> > MS Access MVP
> >
> > ----------------------------------------
> > > From: ciro.carbone@...
> > >
> > > Hi all.
> > >
> > > I do not have any success to try to solve this problem...
> > > I have a simple table containing 10 records. Each record contains a
decimal value. I would like to average these values in a group of 10
contiguous records. For example:
> > >
> > > Value
> > > 5
> > > 10
> > > 2
> > > 7
> > > 23 ----> 9,4
> > > 7
> > > 33
> > > 67
> > > 6
> > > 1 ----> 22,8
> > >
> > > ...so my query result will consist of 2 records like below:
> > >
> > > Results
> > > 9,4
> > > 22,8
> > >
> > > Any tip will be appreciated.
> > >
> > > Many thanks
> > > Ciro
> 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 (5) |
Tidak ada komentar:
Posting Komentar