Report that totals weekly data then grandtotal 1

[cdavisdccb]

I have a crosstab query that returns overtime hours for 12 departments during the work week (Mon - Sat).
I also have a report that displays this data. In the report, I have unbound fields to show 12 running sums (Grand Total). What I need is also the Weekly Total.
I've tried several different suggestions that I found in this forum, but can't quite get what I want. the closest formula I used is:

"WeekEnding: [OTDate]-Weekday([OTDate])", but I can't figure it into the report.

I've gotten the query to give me only the current month by using:
"((Month([OTDate]))=Month(Now())" <--SQL

Without listing all 12 departments, the report is as such:
(Date is "Long Date" format, and skipped dates return nothing since it's a crosstab query)
[tt]
OTDate dept1 dept2 dept3 dept4
(Tue)6/1 0.0 1.5 2.5 0.0
(Wed)6/2 0.0 0.0 3.0 2.0
(Thu)6/3 0.5 1.5 1.0 2.0
(Sat)6/5 0.0 8.0 4.0 0.0
0.5 11.0 10.5 4.0 <--*Week 1 Total*

(Mon)6/7 1.0 1.5 0.0 0.0
(Tue)6/8 1.0 1.0 1.0 1.0
(Wed)6/9 0.5 0.0 1.5 2.0
(Thu)6/10 1.0 1.0 2.0 2.5
(Sat)6/12 4.0 4.0 0.0 3.5
7.5 7.5 4.5 9.0 <--*Week 2 Total*

(Mon)6/14 0.5 1.0 0.0 0.0
0.5 1.0 0.0 0.0 <--*Week 3 Total*

8.5 19.5 15.0 13.0 <--*GRAND TOTAL*
[/tt]
The report/query must be able to determine the appropriate Saturday, and if there's no data for a Saturday (as in Week 3 example), then it sums up to the next Saturday.
I hope I explained this well enough.

If anyone has any suggestions, I'd surely appriciate it!
Chris


&quot;It doesn't matter how idiot-proof you make it, someone makes a better idiot!&quot;

 

[dhookom]

Why and where are you using unbound fields?
Do you have columns in your query for each department?
What is the SQL of your crosstab query?

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

 

[cdavisdccb]

Duane,

[ol]
[li]I am using unbound fields for each department in the report's Report Footer with the Control Source =Sum([dept1]).[/li]

[li]I do have columns for each department in the crosstab.[/li]

[li]The SQL for my crosstab is:[/li]

TRANSFORM Sum([qry main].Hours) AS [The Value]
SELECT [qry main].Date, Sum([qry main].Hours) AS [Total Of Hours]
FROM [qry main]
GROUP BY [qry main].Date
PIVOT [qry main].Dept;

[/ol]

Hope this helps.
Chris


"It doesn't matter how idiot-proof you make it, someone makes a better idiot!"
- Bazooka Joe

 

[dhookom]

If the controls in the Report Footer have a control source value then they are bound.
I would add your dept values to the Column Headings property in the crosstab query. You can also group your report on week intervals of the date field. In the Week footer, you can use the same text boxes as you use in the report footer.

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

 

[cdavisdccb]

Crap, I guess I was overthinking it!

I added a [Week#]field in my query

Week#: Switch(Day([Date])<7,1,Day([Date])<14,2,Day([Date])<21,3,Day([Date])<28,4,True,5)

Which I got from Bob Scriver scriverb at thread703-840187

Then I copied the same unbound "Sum" boxes that were in the report footer to the Week# footer.

Thanks Duane,

Chris

"It doesn't matter how idiot-proof you make it, someone makes a better idiot!"
- Bazooka Joe

 

[dhookom]

I have not heard of using this type of week since a Monday and Tuesday of the same week could return two different values from the expression/function you are using. Is this what you want?

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