@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]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (6) |
Tidak ada komentar:
Posting Komentar