#Crosstab in excel 2013 update#
#Crosstab in excel 2013 code#

If fld.OrdinalPosition > (NonPivotFieldCount - 1) ThenДoCmd.RunSQL 'Insert into ttblSortCrosstabColumns VALUES(' & fld.OrdinalPosition & ', ''' & fld.Name & ''')' ' And populate it with the current index and column names from queryname If Not IsNull(DLookup('Name', 'MSysObjects', 'Name='ttblSortCrosstabColumns' And Type In (1,4,6)')) ThenДb.Execute 'DROP TABLE ttblSortCrosstabColumns'Дb.Execute 'CREATE TABLE ttblSortCrosstabColumns (FieldIndex INTEGER, ColumnName TEXT(250))' ' Then, create a temporary indexing table ' First, get the list of fields from the query Set qdfSRC = db.QueryDefs(querynameSource) ' SortPivotColumns 'qrењњrosstab_Initial', _ДoCmd.SetWarnings False 'Turn off warnings ' 6) An optional array that contains any parameters needed for the query ' 5) A number which specifies the count of non-pivot/row heading columns (NonPivotFieldCount) ' The field in 'SortName' that has the desired order is the SortIndexName ' The field in 'SortName' that the crosstab query columns are joined against is the 'SortColumnNameField' ' The name of the table or query would be 'SortName'

#Crosstab in excel 2013 update#

' 4) A table or query that can be joined on the column names of the cross tab query to update the index table ' 3) An index table which has two columns, a numeric index used for sorting and the column name ' 2) The resulting, columns-have-been-sorted query (query) ' 1) The original unmodified cross tab query (querynameSource) ' This technique uses several components. ' This sub goes through several steps to effectively adds an In list that sorts the 'Columns' of a crosstab query in MS Access Public Sub SortPivotColumns(querynameSource As String, queryname As String, SortName As String, SortColumnNameField As String, SortIndexName As String, NonPivotFieldCount As Integer, ParamArray ParamArr() As Variant) Please see the screen shots below the Sub. The solution is a procedure that needs triggered by a command button on a form or another event. Documentation for this construct is available from MSDN. Doing so will sort the columns in the crosstab query by order of the elements in the pivotfield In list. Having encountered the same scenario various times, I prepared a repeatable way to add an In list to the end of the PIVOT clause.

#Crosstab in excel 2013 code#

| P_Key | This is yet another accounting code! | Yet another accounting code | Hey, this is accounting code X! | However, what I need it to look like is the table below, which is sorted off of the key in Table #1, rather than Description. | P_Key | Hey, this is accounting code X! | This is yet another accounting code! | Yet another accounting code | These are exported to Excel for the user to update. P_Key + F_Key is unique and the two are a primary key on the table. | abR3 | This is yet another accounting code! | | Kfsg2E | Hey, this is accounting code X! | The Key is the sort for the column order, and the Descriptions is the outputted heading in the crosstab. There are a few other complexities surrounded around my live scenario but this data set certainly gets the point across. Here is some very simplistic, sample data that helps express the problem. 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. This is especially an issue since the sort information is system data and useless to the end-user of the crosstab.Дoes 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? 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. 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. this isn't true if your columns are dynamic. This is a real pain but, as one answerer mentioned on another site, 'It's only a pain once!' You can change this order by going to the Properties dialog and entering the Column Headings in the order that you prefer. If you've ever created a crosstab query in Access, you are aware that by default Access sorts your columns in alphabetic order. 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.