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:
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):
And when I mentioned adding an IN line to the end of this, that looked like this:
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:
Code:
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):
Code:
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:
Code:
"IN ('Start', 'Update 1', 'Update 2', 'Update 3', 'Update 4', .... , 'Update 50', 'Finish);" 'The dots are just shorthand here
Last edited: