Ciro-
The simple answer is:
SELECT Avg(Value) As Average, "First 5" As Label
FROM MyTable
WHERE DateField <= #2/5/2013#
UNION ALL
SELECT Avg(Value) As Average, "Next 5" As Label
FROM MyTable
WHERE DateField > #2/5/2013#
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of cc_electronic
Sent: Monday, April 08, 2013 9:52 PM
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
<mailto:MS_Access_Professionals%40yahoogroups.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
>
[Non-text portions of this message have been removed]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (9) |
Tidak ada komentar:
Posting Komentar