 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Reports / Printing / December 2005crosstab query
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.  SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. >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 [quoted text clipped - 8 lines] > How > do I get around this so that that yearly totals will show in the 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. SignatureDuane Hookom MS Access MVP >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 [quoted text clipped - 8 lines] > How > do I get around this so that that yearly totals will show in the report. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.