title: How can I sort the columns in a crosstab query, when the column data is dynamic?
body: <p>I've been doing a bit of research on this topic and I can't seem either find a workable solution, or one that is explained well enough  for me to implement.</p>
  
  <p>If you've ever created a crosstab query in Access, you are aware that by default Access sorts your columns in alphabetic order.  You can change this order by going to the <strong>Properties</strong> dialog and entering the <strong>Column Headings</strong> in the order that you prefer.  This is a real pain but, as one answerer mentioned on another site, "It's only a pain <em>once</em>!"</p>
  
  <p>Well... this isn't true if your columns are dynamic.  In my case, I have a second column on the table that contains the column headings that I'd like to use that field for sorting.  I guess I could append the details of my sort column, to the front of the description column (which has been suggested else where) but I don't feel that this is the most elegant means of solving the problem.  This is especially an issue since the sort information is system data and useless to the end-user of the crosstab.</p>
  
  <p>Does anyone know of a solution to this problem?  If so, could you spell out the steps to sort the dynamic columns of a crosstab query?  </p>
  
  <p>I think the problem is persistent across all versions of Access that are in common use (Access 2003+) but just in case it makes a difference, I am using Access 2010.</p>
  
  <hr>
  
  <p><strong>UPDATE</strong></p>
  
  <p>Here is some very simplistic, sample data that helps express the problem. There are a few other complexities surrounded around my live scenario but this data set certainly gets the point across.</p>
  
  <p><strong>Table #1</strong> 
  This is where the headings come from.  The <code>Key</code> is the sort for the column order, and the <code>Descriptions</code> is the outputted heading in the crosstab.</p>
  
  <pre><code>+---------+---------------------------------------+
  | Key     | Descriptions                          |
  +---------+---------------------------------------+
  | Kfsg2E  | Hey, this is accounting code X!       |
  +---------+---------------------------------------+
  | abR3    | This is yet another accounting code!  |
  +---------+---------------------------------------+
  | Gruu!   | Yet another accounting code           |
  +---------+---------------------------------------+
  </code></pre>
  
  <p><strong>Table #2</strong> This is the store of data
  <code>P_Key + F_Key</code> is unique and the two are a primary key on the table.</p>
  
  <pre><code>+---------+---------+-------+
  | P_Key   | F_Key   | Value |
  +---------+---------+-------+
  | 1001    |Kfsg2E   | 1.0   |
  +---------+---------+-------+
  | 1001    |abR3     | 1.1   |
  +---------+---------+-------+
  | 1001    |Gruu!    | 1.2   |
  +---------+---------+-------+
  | 1002    |Kfsg2E   | 2.0   |
  +---------+---------+-------+
  | 1002    |abR3     | 2.1   |
  +---------+---------+-------+
  | 1002    |Gruu!    | 2.2   |
  +---------+---------+-------+
  | 2001    |Kfsg2E   | 3.0   |
  +---------+---------+-------+
  | 2001    |abR3     | 3.1   |
  +---------+---------+-------+
  | 2001    |Gruu!    | 3.2   |
  +---------+---------+-------+
  </code></pre>
  
  <p><strong>Crosstab Results</strong>
  These are exported to Excel for the user to update.</p>
  
  <pre><code>+---------+---------------------------------+--------------------------------------+-----------------------------+
  | P_Key   | Hey, this is accounting code X! | This is yet another accounting code! | Yet another accounting code |
  +---------+---------------------------------+--------------------------------------+-----------------------------+
  | 1001    | 1.0                             | 1.1                                  | 1.2                         |
  +---------+---------------------------------+--------------------------------------+-----------------------------+
  | 1001    | 2.0                             | 2.1                                  | 2.2                         |
  +---------+---------------------------------+--------------------------------------+-----------------------------+
  | 1001    | 3.0                             | 3.1                                  | 3.2                         |
  +---------+---------------------------------+--------------------------------------+-----------------------------+
  </code></pre>
  
  <p>This is how Access sorts these columns.  However, what I need it to look like is the table below, which is sorted off of the key in <code>Table #1</code>, rather than <code>Description</code>.</p>
  
  <pre><code>+---------+--------------------------------------+-----------------------------+---------------------------------+
  | P_Key   | This is yet another accounting code! | Yet another accounting code | Hey, this is accounting code X! |
  +---------+--------------------------------------+-----------------------------+---------------------------------+
  | 1001    | 1.1                                  | 1.2                         | 1.0                             |
  +---------+--------------------------------------+-----------------------------+---------------------------------+
  | 1001    | 2.1                                  | 2.2                         | 2.0                             |
  +---------+--------------------------------------+-----------------------------+---------------------------------+
  | 1001    | 3.1                                  | 3.2                         | 3.0                             |
  +---------+--------------------------------------+-----------------------------+---------------------------------+
  </code></pre>