Summarize Recors by Day Week Month Quarter Year etc

[efinnen]

Summarize By Day/Week/Month/Quarter etc.

I have a table which has various values, but the two important fields are DATE and CATCH. I need to be able to summarize the CATCH field by day, week, month, quarter and year. This table was generated from an input form which provides start and end date as well as various other parameters.

I was thinking of adding a STEP field in there and using that as a field for my calculations (i.e. all the records with the same day/month/week would have the same record). This is fairly easy to do for the daily summaries with something along the lines of : int([Date]-Min([Date]). However, I am at a loss in regards to how to fill in the STEP field with appropriate values for weekly, monthly etc analysis.

Any ideas or suggestions would be appreciated greatly. Also if there is any better approach to do this then my STEP field approach I’m certainly willing to listen to them

A very very small subset of the tables with STEP populated for days is included below

Thanks Again
-Eric

CATCH DATE STEP
25 8/21/2002 0
43 8/21/2002 0
29 8/21/2002 0
123 8/25/2002 4
585 8/26/2002 5
1200 8/26/2002 5
55 8/27/2002 6
100 8/28/2002 7
98 9/2/2002 12
28 9/3/2002 13
82 9/4/2002 14
77 9/4/2002 14
65 9/4/2002 14
91 9/5/2002 15
45 9/7/2002 17
54 9/8/2002 17
67 9/8/2002 18
23 9/11/2002 21
18 9/12/2002 22
22 9/14/2002 24
48 9/14/2002 24
42 9/15/2002 25
20 9/17/2002 26
18 9/19/2002 29

 

[efinnen]

Never mind... I'm an idiot and just figured out the easy way..

MONTHS

Step: Format([Analysis]![DATE],"m yyyy")

Quarters


Step: Format([Analysis]![DATE],"q yyyy")


etc.

-e

 

[LittleSmudge]

Well first off it is a VERY bad idea to have a object name that is also a system key word.

So rename the Date field in the table to CDate

How about something like

For Monthly summary

SELECT Sum(Catch) As SumOfCatch
     , Month(CDate) As CMonth
     , Year(CDate) As CYear
FROM tblData
GROUP BY Month(CDate), Year(CDate)
ORDER  BY Year(CDate), Month(CDate)

For Annual summary

SELECT Sum(Catch) As SumOfCatch
     , Year(CDate) As CYear
FROM tblData
GROUP BY Year(CDate)
ORDER BY Year(CDate)

and for Quarters then you'll need something like

SELECT Sum(Catch) AS SumOfCatch
     , Year([CDate]) AS CYear
     , IIf(Month([CDate])<4,1,
       IIf(Month([CDate])<7,2,
       IIf(Month([CDate])<10,3,4))) AS Qtr
FROM tblCatch
GROUP BY Year([CDate]), IIf(Month([CDate])<4,1,
                        IIf(Month([CDate])<7,2,
                        IIf(Month([CDate])<10,3,4)));

'ope-that-'elps.



G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[efinnen]

Thanks for the heads up. I'll be giving that a shot right after lunch..

And as far as the field names are concerned. Yup, I'm was aware of that (somewhere in the back of the brain). I changed/simplified the field names a bit before I posted the sample table and wasn't thinking about key words for demo purposes.

Thanks
-e

 

[BoxHead]

I'd suggest avoiding function names like CDate as field names as well. I tripped myself on a field called 'str' not too long ago and it's not on the Reserved Word list either.



John

Use what you have,
Learn what you can,
Create what you need.