Rolling YTD Column Headings in Crosstab Query Report

[ndd25]

I'm new to access and vba. I created a crosstab query with
a. User = Rowheading, b. Date = Columnheading c. rate = value.

Monthly productivity numbers will be added overtime so the column headings need to be dynamic with the months for a rolling ytd metric.

That being said, the report can have a fixed amount of 12 column headings, I just need the labels and values on the report to be dynamic.

How do I do this?

I've seen vba associated with OpenReport events.

[jforbes]

I think this would mostly depend on how your Crosstab is written and how your Report is written. If you used the Wizard to create the Crosstab and then use the Wizard to create the Report, you most likely have columns that are named by the Month, like "Jan", "Feb", "Mar"... If you have this, then you probably only need to put the Start and End Date on the Report.

So what do you currently have and what are you looking to change?

[ndd25]

Thanks jforbes,

I've attached pictures of my query and report designs to help. You're right, the column headings of my report are just the months listed in the data. Right now, I have data only for Jan 2017 and Feb 2017. With time, I will be adding data for each month. In Jan 2018, Jan 2017 will need to drop off and this will need to continue.

Is there anyway to do a separate query to pull the months I want and have that feed into the column headings specified in the properties sheet?

[jforbes]

Crosstabs are some tricky queries, so hopefully this wont get too hairy.

Just playing around with the Access' create Query Wizard allowed me to create a Crosstab that has a SQL like this:

TRANSFORM Count(StudentAttendance.ID) AS CountOfID
SELECT StudentAttendance.StudentID, Count(StudentAttendance.ID) AS [Total Of ID]
FROM StudentAttendance
GROUP BY StudentAttendance.StudentID
PIVOT Format([AttendanceDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

it looks like this in the QBE:

The nice thing about this is that it has the Column Headers without the Year, meaning that it will have the same Name for the Column Headers no matter what year it is.

With something like this, the only thing left to do would be put the date range at the top, and limit the report to a year at a time or otherwise the Months would total across the years. i.e. January 2017 and January 2016 would both be totaled into one number. It would be pretty simple if the report was limited to only one year at a time, but that might not work for you.

I'm curious of what you think.

[ndd25]

Hmm, I think I was hoping for something a little more dynamic (i might have to come up with some vba to do the trick for issues other than months) but for now it'll work just fine! I restricted my query for the previous 11 months and the current month. That way all the data appears from a month associated only with one year. And then I altered the column headings to month excluding the year as you suggested. This solution will work for me. Thank you so much for your help Jforbes.