Dynamic Report

[joanne2468]

I am a basic Access 2010 user and am not familiar with VBA but I try my best to figure things out. My problem is I have a crosstab query that has columns that will change. I created a report and used the following SQL code to include all possible fields for columns in my report. The problem is the report has too much information. Is there away that I could create a dynamic report that will leave null values off of the report and the labels for the fields will change with the changes in the cross tab query.

I apologize if I didn't explain this in a way that is understandable. Here is the SQL code:

TRANSFORM Sum(QryExhibitOnebeforeTotals.ALLOCATED_COST) AS SumOfALLOCATED_COST
SELECT QryExhibitOnebeforeTotals.PROP_UNIT, QryExhibitOnebeforeTotals.ASSET_CLASS, QryExhibitOnebeforeTotals.Property_Unit.DESCRIPTIO N, Sum(QryExhibitOnebeforeTotals.ALLOCATED_COST) AS [Total Of ALLOCATED_COST]
FROM QryExhibitOnebeforeTotals
GROUP BY QryExhibitOnebeforeTotals.PROP_UNIT, QryExhibitOnebeforeTotals.ASSET_CLASS, QryExhibitOnebeforeTotals.Property_Unit.DESCRIPTIO N
PIVOT QryExhibitOnebeforeTotals.LIFE


IN (0000,0100,0300,0500,0600,0700,0900,1000,1200,1500 ,2000,2750,3150,3900,4000);

Thank you for your help.

Joanne

[June7]

You can use VBA code to modify the report. Review:

http://www.blueclaw-db.com/report_dy...stab_field.htm

[joanne2468]

You can use VBA code to modify the report. Review:

http://www.blueclaw-db.com/report_dy...stab_field.htm

Thank you for your help. I looked at the article but couldn't make sense of how to do this.

[June7]

Sorry, VBA is probably the only way to accomplish what you want. Either by writing data to a 'temp' table that has fields for all possible data that could result in columns or by modifying the report.

If the blueclaw article is not clear enough, google 'Access report dynamic crosstab'.