Report showing multiple actions across multiple years

[Hagridore]

In this database that I've been working on for some time, I have a series of four actions that take place and fields that track the dates for these actions. I have created summary queries, using IIF and Totals (Sum), to pull out a number of how many of each events occur in each year. I also have some statistical information tracked on a separate report for each year that tracks percentages as appropriate. What I'd like is a report format that allows me to have the actions across as columns and the years as rows. I'm attaching an image of the kind of thing I have in mind here:



I know I can do this with subreports and labels, but I'm hoping there's a way to do it that will look cleaner and will only require creating one item.

 

[JHB]

Have you looked at a cross tab query?

 

[Hagridore]

I have, and was thinking that was the way to go. But the challenge is that I don't have fields in any table or query called "Year" or that only have the year itself in them. I didn't see any way in their structure to create a crosstab query that pulled the year out of the date and, perhaps, listed only the unique years as row headings.

 

[JHB]

You can do some calculation in the query regarding the year, see attachment.

The SQL string for the above:

SELECT "Year " & (2015-Year([TheDate])+1) AS Expr1, Count(TheDate) AS CountOfTheDate
FROM TheYearTable
GROUP BY "Year " & (2015-Year([TheDate])+1);

If you can't get it, post you database with some sample data, (zip it).