The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Access Crosstab Question

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?
AMerrickanGirl Send private email
Tuesday, March 27, 2007
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.
Kyralessa Send private email
Tuesday, March 27, 2007
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.
Deb Send private email
Tuesday, March 27, 2007
I hate to say it, but this makes me nostalgic for Crystal Reports ... you could display one header in a crosstab but sort by another.
AMerrickanGirl Send private email
Tuesday, March 27, 2007
Hi AMerrickanGirl,

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
The logical approach would be to:
(1) In your base query, force the date in question to be the beginning of the month: DateSerial(Year(TheDate),Month(TheDate),1)
(2) Crosstab on the (full) forced date;
(3) Have the form or report format the date.
Don Edwards Send private email
Wednesday, April 04, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz