Changing Sort of Column Headings in Crosstab Query (1 Viewer)

[mulch17]

Hello everyone,

I am running into an issue with a Crosstab Query that I need to export to Excel. Here's the situation.

The crosstab query has an ID# for the row headings, Comment Type for the column headings, and the actual comment as the value. I would like to have this as the output:

ID  |  Start  |  Update 1  |  Update 2  |  Finish  |  .
1   |  A      |  B         |            |          |  .
2   |  C      |            |            |          |  .
3   |  D      |  E         |  F         |  G       |  .
4   |  H      |            |            |          |  .
5   |  I      |  J         |            |          |  .
.....

My query works, but the Column Headings are always sorted in alphabetical order (Finish -> Start -> Update 1 -> Update 2). I would like to change that sort order. I found this link in the search bar:

http://www.access-programmers.co.uk/forums/showthread.php?t=77786

They provided a few solutions, but they wouldn't be applicable to my situation. Here's the challenge. The solutions in the above thread suggest manually entering the column headings, but I don't know in advance what the column headings would be.

In the example above, I listed 2 updates, but there could be 5 or 10 or 100 updates. I know that I want start as the left column, finish on the right column, and alphabetical in between.

Another poster suggested putting an IN statement at the end of the SQL statement, which lists the values in the order you want them. This works for me, if I put IN('Start', 'Update 1', 'Update 2', 'Update 3', 'Finish'). This does exactly what I want, but again I won't always know how many updates to put in the middle. That's user-selected.

I tried putting in a huge list into the IN statement (like up to 50 updates) and that works, but it always displays a column for all 50 updates, even if 48 of them are blank. I wouldn't want a bunch of unused blank columns.

I've tried a variety of things in addition to what was suggested in that post, but it's proving to be pretty difficult. I feel like there's gotta be a more elegant way of approaching this. Any suggestions out there? Thanks for your time, and thanks for reading!

EDIT:

If it helps anyone, here is the current code that is working properly for me (besides the column sort):

strSQL = "TRANSFORM First([Comment]) AS FirstComment "
strSQL = strSQL & "SELECT [ID] "
strSQL = strSQL & "FROM [CommentTable] "
strSQL = strSQL & "WHERE [ID] IN (" & SelectedIDs & ") " 'SelectedIDs is calculated in a while loop from above, I can post this code if it's necessary
strSQL = strSQL & "GROUP BY [ID] "
strSQL = strSQL & "PIVOT [CommentType];"

And when I mentioned adding an IN line to the end of this, that looked like this:

"IN ('Start', 'Update 1', 'Update 2', 'Update 3', 'Update 4', .... , 'Update 50', 'Finish);" 'The dots are just shorthand here

 

[Minty]

You can query the users selection before constructing your final SQL and dynamically build your IN statement on the fly , then insert it into you final SQL string?

 

[mulch17]

You can query the users selection before constructing your final SQL and dynamically build your IN statement on the fly , then insert it into you final SQL string?

That's the main idea I had in mind, but I'm not sure how to build the IN statement programmatically with the proper format. Would you store the user's selections as a recordset, and then loop through the comment type field for each record, and tack it on to a string?

If that would work, how would you do this in VBA? I think I understand the idea, but I'm not sure how I would code it. It seems more complicated than I was expecting, but if it's what needs to be done, I can deal with it.

 

[mulch17]

The other alternative is to make a static IN statement with an arbitrary number of update columns (the max number we would ever expect to see), and then once the spreadsheet is exported, use VBA to delete all the extra blank columns, but I haven't gotten that to work either. I've gotten it working using the SumProduct formula within Excel, but not with an Access VBA command.

 

[Minty]

That's the main idea I had in mind, but I'm not sure how to build the IN statement programmatically with the proper format. Would you store the user's selections as a recordset, and then loop through the comment type field for each record, and tack it on to a string?

If that would work, how would you do this in VBA? I think I understand the idea, but I'm not sure how I would code it. It seems more complicated than I was expecting, but if it's what needs to be done, I can deal with it.

You have the right idea - it's not as bad as it sounds - loop something like this (completely Air code you'll need to get it properly written)

sSql_IN = "IN ('Start'" [COLOR="Green"]  'The start of the IN statement[/COLOR]

[COLOR="Green"]  ' In the loop code where you have created a record set with your users column headers available to you [/COLOR]

For each ColumnHeaditem
     sSql_IN = sSQl_IN & ", '" & ColumnHeaditem & "' "
Next ColumnHeaditem

sSql_IN = sSql_IN & ", 'Finish' );"    [COLOR="Green"]' The closing of the In statement [/COLOR]

Then add sSql_IN to your working SQL code for your crosstab.

If that's not something you can do, I don't have time currently to do the whole code now but I can probably help more tomorrow if someone else can't assist.

 

[mulch17]

You have the right idea - it's not as bad as it sounds - loop something like this (completely Air code you'll need to get it properly written)

sSql_IN = "IN ('Start'" [COLOR="Green"]  'The start of the IN statement[/COLOR]

[COLOR="Green"]  ' In the loop code where you have created a record set with your users column headers available to you [/COLOR]

For each ColumnHeaditem
     sSql_IN = sSQl_IN & ", '" & ColumnHeaditem & "' "
Next ColumnHeaditem

sSql_IN = sSql_IN & ", 'Finish' );"    [COLOR="Green"]' The closing of the In statement [/COLOR]

Then add sSql_IN to your working SQL code for your crosstab.

If that's not something you can do, I don't have time currently to do the whole code now but I can probably help more tomorrow if someone else can't assist.

Thanks for your quick reply again. This part has turned out to be pretty easy actually, I can construct the IN statement with the proper format. Now I need to tackle sorting it on the fly. Depending on the order of what they checked, it's possible that sSQL_IN could be:

'Start', 'Update 3', 'Update 4', 'Update 2', 'Update 1', 'Finish'

I would just apply the proper sorting on the form where they're choosing the the records, but that's already being sorted by ID#. I'll keep thinking about it, and I'll of course reply back if I figure it out, but I did get that part of it working. Thanks Minty!!

 

[mulch17]

You have the right idea - it's not as bad as it sounds - loop something like this (completely Air code you'll need to get it properly written)

sSql_IN = "IN ('Start'" [COLOR="Green"]  'The start of the IN statement[/COLOR]

[COLOR="Green"]  ' In the loop code where you have created a record set with your users column headers available to you [/COLOR]

For each ColumnHeaditem
     sSql_IN = sSQl_IN & ", '" & ColumnHeaditem & "' "
Next ColumnHeaditem

sSql_IN = sSql_IN & ", 'Finish' );"    [COLOR="Green"]' The closing of the In statement [/COLOR]

Then add sSql_IN to your working SQL code for your crosstab.

If that's not something you can do, I don't have time currently to do the whole code now but I can probably help more tomorrow if someone else can't assist.

Alright, I finally got it figured out. It was a doozy, but it was fun to think through this and use some creative magic here!

For anyone out there reading, here's what I ended up doing:

SelectedIDs = ""
SelectedTypes = ""
MaxUpdateNumber = 0

Me.[SubformName].Form.Recordset.MoveFirst
While Not Me.[SubformName].Form.Recordset.EOF
   If Me.[SubformName].Form.Recordset("Select") Then 'This is what checks if the record has a checkbox next to it
      SelectedIDs = SelectedIDs & ", '" & Me.[SubformName].Form.Recordset("ID") & "'"

      If Me.[SubformName].Form.Recordset("Type") = "UPDATE" Then
         If CLng(Me.[SubformName].Form.Recordset("Update #")) > MaxUpdateNumber Then 'This will find the highest update # that the user selected
            MaxUpdateNumber = CLng(Me.[SubformName].Form.Recordset("Update #"))
         End If
      End If

   Me.[SubformName].Form.Recordset.MoveNext

Wend

SelectedIDs = Mid(SelectedIDs, 3) 'Gets rid of the first ", " before the list of selected IDs

For i = 1 to MaxUpdateNumber
   SelectedTypes = SelectedTypes & ", 'Update " & CStr(i) & "'" 'Builds the string that contains the list of update numbers
   'This will lead to blank columns if the user only selects high update numbers, but that's very unlikely and won't be a dealbreaker
Next i

SelectedTypes = "'Start'" & SelectedTypes & ", 'Finish'" 'This is the IN statement clause

strSQL = "TRANSFORM First([Comment]) AS FirstComment "
strSQL = strSQL & "SELECT [ID] "
strSQL = strSQL & "FROM [CommentTable] "
strSQL = strSQL & "WHERE [ID] IN (" & SelectedIDs & ") "
strSQL = strSQL & "GROUP BY [ID] "
strSQL = strSQL & "PIVOT [CommentType] "
strSQL = strSQL & "IN (" & SelectedTypes & ");"

I'm surprised that it was this complicated, but I've got it working now, thanks very much for your help Minty!!!

 

[Minty]

Really glad you got it figured out - to be honest although it looks complicated it's actually a pretty common practice to build statements like this to achieve what your "customers" are after