Grouping Dates

[Lou]

Good Day;
ACCESS 2002
Report info;
Based of - tblCouponHistory
[DateReceived]
[NumberReceived]
[HistoryID]

I am trying to group the history by date in the report by
the Week and just list the subtotal. what would be my best
approach, using the grouping and sorting of the
[DateReceived] but cant seem to fill just those 4 weeks.

Any ideas or suggestions or should I try and write a
formula cell?

Thank you.

 

[Jim/Chris]

In your report go to sorting and grouping. In here group
by your dateRecieved field (ascending) and set the interval
to Weekly. Then add a field and Sum([NumberRecieved])

Jim

 

[Fons Ponsioen]

Have you tried using a summing query grouped by year and
week and select only the top 4, this should give you
subtotals for the last 4 weeks.
Hope this helps
Fons

 

[Lou]

Thank you Jim & Thank you Fons

Fons can you give me an example of this..? Kinda intrugued
on the query by year a top 4 thing...

Thank you

-----Original Message-----
Have you tried using a summing query grouped by year and
week and select only the top 4, this should give you
subtotals for the last 4 weeks.
Hope this helps
Fons

-----Original Message-----
Good Day;
ACCESS 2002
Report info;
Based of - tblCouponHistory
[DateReceived]
[NumberReceived]
[HistoryID]

I am trying to group the history by date in the report by
the Week and just list the subtotal. what would be my best
approach, using the grouping and sorting of the
[DateReceived] but cant seem to fill just those 4 weeks.

Any ideas or suggestions or should I try and write a
formula cell?

Thank you.
.

.

 

[Fons Ponsioen]

The following is the sql for a sample query:

SELECT TOP 4 [_Strms].Date_ AS [Year], [_Strms].Date_ AS
Week, Sum([_Strms].Value_) AS SumOfValue_
FROM _Strms
GROUP BY [_Strms].Date_, [_Strms].Date_
ORDER BY [_Strms].Date_ DESC , [_Strms].Date_ DESC;

_Strms is the table name.
Date_ is looked at twice, once formatted as year and once
formatted in week number.
Value_ was just a data element to sum.
Give it a try.
Hope this helps.
Fons

-----Original Message-----
Thank you Jim & Thank you Fons

Fons can you give me an example of this..? Kinda intrugued
on the query by year a top 4 thing...

Thank you

-----Original Message-----
Have you tried using a summing query grouped by year and
week and select only the top 4, this should give you
subtotals for the last 4 weeks.
Hope this helps
Fons

-----Original Message-----
Good Day;
ACCESS 2002
Report info;
Based of - tblCouponHistory
[DateReceived]
[NumberReceived]
[HistoryID]

I am trying to group the history by date in the report by
the Week and just list the subtotal. what would be my best
approach, using the grouping and sorting of the
[DateReceived] but cant seem to fill just those 4 weeks.

Any ideas or suggestions or should I try and write a
formula cell?

Thank you.
.

.

.