Binding TRANSFORM query in Access to a report

Question

Whats the best way to bind variable column names to a report field in Access when using a crosstab query?

Answer 1

This page has an exhaustive example of setting up a dynamic column ("crosstab"-type) report in Access.

http://www.blueclaw-db.com/report_dynamic_crosstab_field.htm

(From google search: access transform query report)

Answer 2

The best article I found for binding columns from a crosstab query to a report is from ewbi.develops's notes.

Specifically,

PARAMETERS foryear Short;
TRANSFORM Sum(mytable.amount) AS total
SELECT mytable.project
FROM mytable
WHERE mytable.year In ([foryear],[foryear]+1)
GROUP BY mytable.project
PIVOT IIf(mytable.year=[foryear],"thisyear","nextyear") IN ("thisyear", "nextyear");

This only displays two columns that can be bound as needed.