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
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