crosstab query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I created my first crosstab query. The crosstab query is designed to
calculate the total amounts paid to our customers by year (i.e. in 2005 Joe
Smith recieved $5,000). The information is displayed as follows: rows are
customers and columns are the years with the totals. When I try and make the
report it will not let me it says it does not recognize the Check/Wire Date.
The column is formatted as follows: Format([Check/Wire Date]),"yyyy"). How
do I get around this so that that yearly totals will show in the report.
 
In your crosstab, you will see column names such as:
2003 2004 2005

Those are the field names you will need to use in your report.

Because the field names change depending on what years of data are available
at the time the report is run, you will need to use the Column Headings
property of the crosstab (Properties box, in query design) to list all the
years that could appear on the report. You can then safely use those field
names in your report.
 
I normally create this type of crosstab with "relative dates". For instance
your crosstab might have a column heading expression of:
Field: "Yr" & DateDiff("yyyy",[Check/Wire Date],Date())
Crosstab: Column Heading

Then set the Column Headings property of your query to:
Column Headings: "Yr0","Yr1","Yr2","Yr3"
This would produce columns where Yr0 is always the current year, Yr1 is last
year, etc.
 
Back
Top