Solved Crosstab Query - Dynamic Column Sorting (1 Viewer)

AndyC88

Member
Local time
Today, 06:46
Joined
Dec 4, 2020
Messages
44
Hi all,

I have a Crosstab Query with a large number of columns (~100) which require to be in a custom order.

I have achieved this by sorting and concatenating the column headers into a string within an unbound text box ("Equipment53","Equipment54","Equipment55","Equipment56" etc...) and pasting that into the column headings box in the properties tab in the Design View.

The problem is the column headings are likely to change in the future. I have tried defining the unbound text box as a parameter within the crosstab Query in the PIVOT IN clause but it returns an error (SYNTAX error in transform statement).

Is there any way to achieve this in SQL? Or is there a way to use VBA to update the Column Headings in the Crosstab Query?

Grateful for any assistance!

Thanks,

Andy
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:46
Joined
May 7, 2009
Messages
19,094
maybe add the "heading" in a table with a single record.
then add the table in your xtab query.
concat the fieldname of the table to the field of your xtab table to
produce the heading.
 

AndyC88

Member
Local time
Today, 06:46
Joined
Dec 4, 2020
Messages
44
maybe add the "heading" in a table with a single record.
then add the table in your xtab query.
concat the fieldname of the table to the field of your xtab table to
produce the heading.

Sorry arnelGP - I don't follow? There are multiple column headings?
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 23:46
Joined
Oct 29, 2018
Messages
21,322
Hmm, the only way I could think of having a dynamic column heading is to modify the query using a QueryDef object. Maybe there's another way...
 

AndyC88

Member
Local time
Today, 06:46
Joined
Dec 4, 2020
Messages
44
Hmm, the only way I could think of having a dynamic column heading is to modify the query using a QueryDef object. Maybe there's another way...

I think that's the solution that was used here but I couldn't get it to work / follow the code to apply to my db.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 02:46
Joined
May 21, 2018
Messages
8,439
The big issue here is that the number of columns exceeded the amount that could be defined in the query definition. So it cannot be done the traditional way.
 

AndyC88

Member
Local time
Today, 06:46
Joined
Dec 4, 2020
Messages
44
The big issue here is that the number of columns exceeded the amount that could be defined in the query definition. So it cannot be done the traditional way.
What I found was that when I pasted the concatenated string into the column header property and viewed the query, it worked as intended.

When you go back into the query editor it then throws an error (something like exceeded the 1024 character limit) but it does work if you don’t go back into the query editor.

So there’s no way to reference the value of a text box after the PIVOT IN clause?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:46
Joined
May 7, 2009
Messages
19,094
this is somewhat similar to the Link you shared.
it does not create new table but instead create new select query.
 

Attachments

  • Database1.accdb
    704 KB · Views: 554

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:46
Joined
Feb 19, 2002
Messages
42,872
Here is a sample that creates a report out of a crosstab query. The process substitutes numerical values for the column headings so the crosstab always returns 1, 2,3,4, etc. I limit the number of columns because reports have limited horizontal space but if you are going to export the query you won't need to limit the columns.
 

Attachments

  • BoundDenormalizedForm_20210319.zip
    1.5 MB · Views: 590

AndyC88

Member
Local time
Today, 06:46
Joined
Dec 4, 2020
Messages
44
this is somewhat similar to the Link you shared.
it does not create new table but instead create new select query.

@arnelgp - that looks absolutely spot on. Hopefully an easy fix - the new query that is created does not have the previous cross tab fields in by default (they appear and you can add them in qry editor and it works great).

How would I get these fields in the new sorted query (they are things like employeeID, BranchID etc)?

Thanks!
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:46
Joined
May 7, 2009
Messages
19,094
i added another column to XTabOrder table (newXTabColumn).
you can define/type the New Column name of each Column of crosstab.
see XTabOrder table.
 

Attachments

  • CrossTabColumns.accdb
    500 KB · Views: 528

AndyC88

Member
Local time
Today, 06:46
Joined
Dec 4, 2020
Messages
44
@arnelgp sorry that's not what I meant. There are ~10 fields that are defined as the "row headings" in the original crosstab. These are required in the sorted crosstab as this gets exported to excel to use by another department.

When the new SELECT query (the sorted crosstab) is generated - the "original" row headings are not present.

I think it's this bit of code that it would need adding to?

Code:
sq = Left$(sq, Len(sq) - 1) & " from [" & sXTab & "];"
'open/create new query
On Error Resume Next
Set qd = db.QueryDefs(sNewSelect)
If Err Then
    Set qd = db.CreateQueryDef(sNewSelect, sq)
    db.QueryDefs.Append qd

Thanks so far!
 

AndyC88

Member
Local time
Today, 06:46
Joined
Dec 4, 2020
Messages
44
Attached example to show what I mean - you'll see the other row headings as an example; these would need to be displayed in the sorted xtab.
 

Attachments

  • CrossTabColumns(2).accdb
    492 KB · Views: 538

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:46
Joined
May 7, 2009
Messages
19,094
When the new SELECT query (the sorted crosstab) is generated - the "original" row headings are not present.
you mean the Columns on the Original Crosstab is missing on the Newly created?
or do you want to Add additional Columns not in Original Crosstab?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:46
Joined
May 7, 2009
Messages
19,094
just add those Column to table XTabOrder
 

Attachments

  • CrossTabColumns(2).accdb
    516 KB · Views: 323

AndyC88

Member
Local time
Today, 06:46
Joined
Dec 4, 2020
Messages
44
@arnelgp - thank you, I managed to get this working with an APPEND Query to add the sorted field / column names to the sort table.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:46
Joined
May 7, 2009
Messages
19,094
good to know :)
 

AndyC88

Member
Local time
Today, 06:46
Joined
Dec 4, 2020
Messages
44
good to know :)

Well... one step forward and two steps back...! I remembered the reason why I wanted to specify the column names... I need all the columns displayed whether they have a value in or not. Using your method sorts the columns but still the only data that's displayed are the ones with a value.

I see that if you add a value to your table and specify it in the xTabSort table it will list even if there's no value. Problem is that I'm using Queries to generate the info for the Crosstab.

I suppose a Make Table Query followed by an Append Query (with the list of equipment) would work? (To then generate the crosstab qry from) Just seems a bit messy - is this / would this be common practice? Does it matter if it works?
 

Users who are viewing this thread

Top Bottom