Crosstab Report Question

[Ray1127]

I have a Report that needs to present the Data in the following format:

Jan Feb Mar Q1 Apr May Jun Q2 ....
Approved 1 1 2 1 1
Level 0 3 3 2 2
Level 1 2 1 3 1 1


If I use a crosstab query as the source I lose the months that do not have any data. I've tried using the DCount Function but I don't get anything close to what should be there. I'm Using Dcount([ID Field],"ReportSourceQuery",Month([Date Field])=1 (2,3 Depending on which month) And I'm getting the Grand Total of the report seems Randomly throughtout the report. Anybody have an idea how I can accomplish this report. I've already spent several days and am getting nowhere.

Thanks for any assistance.

 

[dhookom]

What's wrong with using a basic crosstab query with month as the column heading? You can set the Column Headings property to make sure all months are rendered. Quarter totals can be calculated in the report by adding the months.

Duane
Hook'D on Access
MS Access MVP

 

[Ray1127]

That's what I started out but the issue was if there was no data for January there was no column for January. How do I set the Column Headings property to make sure all months are rendered? That's the problem I'm having.

 

[dhookom]

Simply enter "Jan","Feb","Mar",..."Dec" into the [blue]Column Headings property[/blue] of your crosstab query.

Duane
Hook'D on Access
MS Access MVP

 

[Ray1127]

Thanks Duane worked perfectly.