Crosstab reports...I think?

[jrjoneseng]

I am having trouble with an Access 2007 report and/or query. I think I want to do a crosstab type of report, but it is different than the examples that I have found. This may be a table design issue, but I've inherited that...so:
-I have a table that has fields for year, month, active projects, cancelled projects, completed projects, etc.
-I want to display this data in a report with months across the top (once I've filtered the year I want)
-Each row will be a different field: active projects, cancelled projects, etc.
-From what I gather, a cross tab is looking to summarize one field based on variable entries in that field. I want my rows to be individual fields.

I apologize ahead of time if I'm being dumb here...but I think I've taken the wrong path!

 

[dhookom]

It would help if you provide the actual table and field names with the data types and sample records.

I expect you first need to normalize your data with a union query and then build your crosstab.

SELECT Yr, Mth, "Active" as Status, [Active Projects] as NumOf
FROM tblNoNameGiven
UNION ALL
SELECT Yr, Mth, "Cancelled", [Cancelled Projects]
FROM tblNoNameGiven
UNION ALL
SELECT Yr, Mth, "Completed", [Completed Projects]
FROM tblNoNameGiven;

Then build your crosstab based on the union query with Status as the Row Heading, Mth as the Column Heading, and Sum(NumOf) as the Value.


Duane
Hook'D on Access
MS Access MVP

 

[jrjoneseng]

Duane,
I can send more when I get into the office in the morning, but will try to describe better.
My table called "stewardship" contains the following fields:
-month - numerical month
-year - yyyy
-Active - records number of active projects summarized from a different table for the month, integer
-Cancelled - records num of cancelled projects in the month, integer
-Completed - records number of completed jobs in the month
-etc...

Basically the table saves a snapshot each month for trending of metrics.

For the current year, I want a report with months across the top and rows for each field with the specific number of projects falling under the specific month.

Hopefully this makes more sense. If not, I will upload the table tomorrow. I also have a spreadsheet for what the report should look like.

Thanks for the help

 

[dhookom]

I believe my suggestion of a normalizing union query is the best place to start.

Duane
Hook'D on Access
MS Access MVP

 

[jrjoneseng]

Duane,
Thanks. That worked great!

Okay, now one follow on...any ideas how to show extra months across the top of the table when there is no data for those months yet?

For instance, assume you are in July and have the new table created by the union and crosstab queries. You would have columns Jan, Feb, ..., July with rows of data for each of the status "Active", "Completed", etc. There are no monthly entries for Aug through Dec, but I would like there to be blanks.

Any ideas?

 

[dhookom]

You can set the Column Headings property of the crosstab query to dsiplay all months even if there is no data.

Duane
Hook'D on Access
MS Access MVP

 

[jrjoneseng]

Duane,
Where is the setting to adjust the column headings to show all months even if there is no data?

 

[dhookom]

Open the query properties to find the Column Headings property.

Duane
Hook'D on Access
MS Access MVP