Unanswered: Dynamic Building of Multicolumn Crosstab Query
Here is my dilemma. I want to dynamically create a multi column crosstab
query and display the respective results in a report.
I have a table with multiple measures for multiple facilities for multiple qtrs. Each row contains a Numerator and a Denominator. A required field for the report is a Rate field which is num/den where den<>0
So each row will have
FacID, Measure, QtrYear, Num, Den
This is easily turned into a crosstab query for only one value at time.
facID, Measure - row headings
QtrYear - column headings
Num - Value
Issue: I can only do one value at a time
Issue: I want to be able to report for only the most current 4 qtrs for which I have data
Issue: The column names will constantly change if I join together each of the three cross tab queries to make a multi value
What I tried:
I tried dynmacally creating a table, with a column for each qtr for each num, den, and rate. then cycle through the existing table and plug the values in for each of the columns for each of the respective rows.
Does anyone have any idea on this one? I am sick of pounding my head on my desk on this one....
There is a great example of a solution that I have been working with that may help you. It uses a multiple value entry for each cell of the crosstab query, allowing you to pull the values out of the cell in the report using code.
Search for crosstab and report in msdn.com and there will be a response by Duane Hookum with the link in it for the website where the example is downloadable for you.