Annual "detailed" summary report

[Aronvitki]

:banghead:Hello, I'm new to this forum. I need to see if this can be feasible. I need an annual training summary report, but with non-aggregate information in the detail, sorted into monthly columns. Here's an example of what I want:
................|...... Jan........|...... Feb........|...... Mar......|
Category 1 | Day | Hours | Day | Hours | Day | Hours |
................| Day | Hours | Day | Hours | Day | Hours |
Category 2 | Day | Hours | Day | Hours | Day | Hours |
................| Day | Hours | Day | Hours | Day | Hours |

The day is the day in the month and the hours is the number of hours spent training in that category. The number of days in each monthly column is dynamic.

I tried to use subreports for each column within the category, but I get an overflow error since I'm not sure how to set the parent/child properties to filter each "detail list" by month. All data is normalized, so there's no problem there, and I can't use a pivot table because not only do I need to show all months, but also there's a report header that's needed also. I'm hoping I can get some help on this.

 

[Aronvitki]

By the way, don't worry about the dynamic listings of each month. I already know how to deal with that.

 

[vbaInet]

I see a possibility of a Crosstab query here.

1. Create a query to calculate the Hours and Day.
2. Your Day should be formatted in the two digit format, i.e. the 1st should be 01
3. Concatenate the Hours and Day together, i.e. [Day] & " | " & "Hour"
4. Create a crosstab query based on the result.

The Categories would have to report as it's a crosstab query.

 

[Aronvitki]

I tried that, but that way only calculates 1 day per month--either the first or last. I need every day within the month that training has been conducted. I already knew the categories would have to report. I'm just trying to get the "monthly detail" done.

 

[vbaInet]

What do you mean by it only calculates one day per month? If you group by Category, Month, followed by Day and sum the hours it should give you your desired list of records. Then use that in your crosstab query.

 

[Aronvitki]

I didn't quite think of the grouping maneuver in conjunction with it. I'll try it and let you know.

 

[vbaInet]

No probs!!!