Calculated Report Results for Each Week of the Year

[xeb]

How do I have a report return calculated results for each week of the year? In other words, my table contains many records with many fields and I want the report to produce averaged results for designated fields for each week of the year. I think it's an "IIf Statement" but I'm not sure how to write it.

 

[dhookom]

You need to share more than you have. I assume you have a date field (with a name) as well as at least one table. It would also help to see some sample data and sample output. You may want to check the link at the bottom of my signature.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[xeb]

I have a table with quite a few fields, one of which is a date field. All the other fields contain numbers between 1 and 3 which are calculated on my report as averages in four catergories (C) and a total average for all the catergories. At the present time, the report calculates every record in the table.

I need a report that will separate the data into weeks. My data is for a school, so there are not any records for weekend days. Every record will always be Monday through Friday.

The report should look something like this:

C1 C2 C3 C4 TOTAL
Week of 1-5-04 1 1 1 1 1
Week of 1-12-04 1 1 3 3 2
Week of 1-19-04 2 2 2 2 2
Week of 1-26-04 3 3 3 3 3
(Etc.)

(For some reason this example will not maintain its form when I preview it, but I hope it still gives you an idea of what I need.)

I hope this is enough information for you to help me.

Thanks

 

[dhookom]

To maintain formatting in a posting to these forums place the tgml tags around your display like the following except remove the spaces.

[ Code]
                    C1  C2  C3  C4   TOTAL
Week of 1-5-04      1   1   1   1    1
Week of 1-12-04     1   1   3   3    2 
Week of 1-19-04     2   2   2   2    2 
Week of 1-26-04     3   3   3   3    3
[/ Code]

You can create a totals query that groups by
WeekOf: DateAdd("d", -WeekDay(NamelessDateField)+1, NamelessDateField)
Average the other fields.
Do you actually have fields like C1-C4 in your table or is this the result of a query? If so, I fear this might be a bit un-normalized. However, if you can make it do what you want, then I guess it's ok.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.