Report from crosstab how to update for monthly reports

[GinaFlan]

This is a report I created from a crosstab query. I have a form called Scorecard where Employees will add data each month. My plan is a button on the form for them to generate the report. I did a test and added data for October, and the crosstab query updated but the form did not. Is that because it is a crosstab? If I write code in the sql statement, can I generate columns for all months of the year, so the report will update automatically.

[ranman256]

Correct, crosstabs can change depending on the data.
you cant report a crosstab unless you map every possible outcome.

SO..
make a 'report' table that HAS every possible field outcome,
empty the table
append the crosstab data
run report

[GinaFlan]

Ah ok I will try that thank you

[GinaFlan]

Child Nutrition Query.pdf

The attached is a report with the crosstab query.


I used the "in" statement to create the columns for the months, but now the department head would also like the comments as a column so that means multivalue crosstab I will send more info as I research. I am at home, this laptop is slow. She also wants the report to query two month previous from current date, which would display 3 months. Anyway going to figure that out too. I'm wiped out lol


[TRANSFORM Max(Scorecards.Results) AS MaxOfResults
SELECT Scorecards.Groups, Scorecards.Goals, Scorecards.Benchmarks, Scorecards.Comments, Scorecards.MetGoal
FROM Scorecards
WHERE (((Scorecards.Groups)="Child Nutrition"))
GROUP BY Scorecards.Groups, Scorecards.Contact, Scorecards.Goals, Scorecards.Benchmarks, Scorecards.Comments, Scorecards.MetGoal
PIVOT Scorecards.ForTheMonth in ('January','February','March','April','May','June' ,'July','August','September','October','November', 'December')]

[GinaFlan]

Set the view to two pages for the pdf it looks better