rolling 12 month report

[tngirl]

I was hoping I could create this report with a crosstab query, but was told that was not the best tool for this report. I'm looking to create a report of employee performance. The table this information is pulled from is titled Employees. I'm calculating the score with ([Count(FINALSCOREID=2)\(Count(FinalScoreID)]). I would like the report to generate with a start date so any 12 months can be retrieved. I've attached an image of how I would like the report to look. Can I create this report without VBA? any help you can provide is greatly appricated...I've wasted so much time trying to accomplish this with a crosstab query. Thank you.

[June7]

Why won't a CROSSTAB work? I referenced articles in your other thread that describe CROSSTAB technique to do this. However, the CROSSTAB won't show the headings as month name, they will be generic Mth0, Mth1, etc.

The Mar-13, Apr-13, etc headings will have to be calculated in textbox based on the date entered on form as the start date to filter records. The calc for Mth0:

=Format(Forms!formname!start, "MMM-YY")

For Mth1

=Format(DateAdd("m",1,Forms!formname!start), "MMM-YY")

etc.

This does assume there will be at least one record for every month.

Otherwise, VBA and a temp table is only alternative.

[tngirl]

I was told that I can't do a calculation in a crosstabl query, is that not true? The crosstab query works great until I add the calculation. Is the syntax the issue do you think? this is what I'm using ([Count(FINALSCOREID=2)\(Count(FinalScoreID)]). Thanks so much for your help. The crosstab query would be ideal if I can just get it to do the calculation.

[June7]

Some calculations are possible, usually to concatenate fields or extract strings from fields, to create a new column, row, or value field. What you show doesn't make sense to me. Why does that calc need to be in CROSSTAB? Possibly do it in textbox on report.

[tngirl]

If I create a standard report, how is it possible to get the rolling month/year column headings based on a report start date?

[June7]

Method is described in post 2.

[tngirl]

I am thoroughly confused. I'm just trying to create this simple report. I don't know why I am making this so hard. Crosstab formats the report exactly like I would want it to be but I can't get the calculation to work. Now you say to build a regular report....I don't understand how to sort the monthly data. Is there samples of VBA that can help me, or how do I do this temp table you speak of. I'm not a beginner at Access but I'm not overly experienced either, I just need some help please.

[June7]

A standard report structure will list records vertically. Report Grouping and Sorting can organize the records by grouping criteria. Summary calculations can be done in group and report footers. This is basic Access reporting functionality.

If you want to build report based on CROSSTAB, can be tricky because the field headings often change every time the CROSSTAB is run (in your case, the year part changes). Bound textboxes error if the fieldnames are different. So building a report to run perpetually without having to edit the ControlSource of each textbox every time requires special methods.


VBA would be required if want to write data to a temp table and base report on that table. Here is one example http://forums.aspfree.com/microsoft-...ry-322123.html