A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.
I've recently become responsible for an Access database.
The users have asked for several cross-tab queries grouped by month. They would like the columns to display as "Jan 2007", "Feb 2007", etc. in proper order.
The problem is that Access insists on displaying the columns in alpha order ("Jan 2007", "July 2007", etc.). I can jimmy it by forcing the column headers to display something like "2007 (1) Jan", but that's clunky and the users don't like it.
I can't hard code the column names inside the SQL Pivot statement because the years don't necessarily go from January to December. In fact, some of the queries are for time spans of more than or less than 12 months.
I've searched the web for workarounds and haven't found a good one. Any suggestions?
I read this and thought that if you'd asked me three years ago, I could've told you how to do it.
Then I started trying to do it. Never mind. This appears to be simply a major shortcoming of Access crosstab queries. If you use the wizard, it sorts by date...but that's because it specifies column header names, which you've already mentioned won't work for your case.
It's pretty ridiculous that a query perfectly designed to show columns of data by date won't let you specify the format of that date...but it seems to be the case nonetheless. I think I must have ended up just using the standard short date format for everything.
Yes, this is an annoyance. If the users want direct access to the output of the query itself, then your only option is to give them column headings which contain only numbers (thus forcing it to sort in the correct order).
However, if they would be happy with an Access report, then you have a little more flexibility. You can use the crosstab query as the data source for the report and programmatically control label values when the report opens. The only downside to the "report approach" is that you now have to deal with the possibility of a variable number of columns.
You can manually change the ColumnHeadings property to alter the column sequence (have a look at the Query Properties form in the Query Editor). However this is fixed for all executions of the CrossTable.
If you want the sequence of columns to differ each time you run the query you'll need to do this programmatically by editing the PIVOT clause of the SQL statement. Use these as a starting point:
Essentially you need to reference the query in code, edit the SQL to update the ColumnHeadings property. You'll need to build the ColumnHeadings dynamically adding the headings in the sequence in which you want them to appear. Have a look here:
Note also that (column) values you do not list are excluded from the query result. Also if you include a column heading, but there is no data for that heading, the empty column will still appear.
Marcus from Melbourne
Tuesday, March 27, 2007
This topic is archived. No further replies will be accepted.Other recent topics
Powered by FogBugz