[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1067
  • Last Modified:

Column headings in crosstab queries

Greetings
Is it possible create crosstab column headers so that they automatically reflect whatever data values are stored in the column heading field. If yes, how do I do this?
Thanks
0
ohgee
Asked:
ohgee
  • 13
  • 12
  • 11
  • +1
1 Solution
 
harfangCommented:
A cross-tab uses the expression or field provided as column title. If needed, the expression is converted to string before.
Often, you will want to control that process. Examples:

    Format(datEndDate, "yyyy-mm-dd")
    "DoW_" & WeekDay(datSomeDate)
    IIf( IsNull( curAmount ) , "(unknown)", Format( curAmount, "Standard" ) )

etc.

All possible column titles can be entered in the query property "column headings" so that the column is created even if all values are blank.

Hope this helps
0
 
ohgeeAuthor Commented:
Thanks Harfang
The problem is that if I hard-code them into the "column headings" query property, then each time the data value changes I have to change the "column headings" query property.
Want to do it so that the column headings automatically reflect the data values, without having to enter them into the query property. For example one crosstab query has the values "agree", "error" and "developmental" as the column headings. But what if "developmental" changes to "needs more training". Can this change be reflected without having to type "needs more training" into the query property "column headings" ?
0
 
nico5038Commented:
In a report you can fill the fields by their relative position and set the column equal to the field "value-name".
I used the following code in a similar case:

To start, doing this you need to place the fields "coded" in the report.
The column headings should be called "lblCol1", "lblCol2", "lblCol3", etc.
The "detail" fields should be called "Col1", "Col2", "Col3", etc.

The report query has two rowheader columns and a Total column, therefor the first field is effectively column 4 (count starts at 0 so I used intI=3) but this could differ for you.

Make sure that the number of Columns is not bigger as the number placed. The programcode has no protection against that !

The OpenReport code:

Private Sub Report_Open(Cancel As Integer)
Dim intI As Integer
Dim intR As Integer

Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset(Me.RecordSource)

'Place headers
For intI = 3 To rs.Fields.Count - 1
Me("lblCol" & intI - 1).Caption = rs.Fields(intI).Name
Next intI

'Place correct controlsource
For intI = 3 To rs.Fields.Count - 1
Me("Col" & intI - 1).ControlSource = rs.Fields(intI).Name
Next intI

'Place Total field
Me.ColTotal.ControlSource = "=SUM([" & rs.Fields(2).Name & "])"

End Sub

The report query has two rowheader columns and a Total column, therefor the first field is effectively column 4 (count starts at 0 so I used intI=3) but it could differ for you.

Nic;o)
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
harfangCommented:
For a cross-tabl *query*, simply remove the column headings. They will be updated each time. But you can't then use the query as source for a form or report.

A variant of Nic's solution:

Use a lookup table for the values in the field, storing only the number. The table looks like this:
StatusID StatusName
     1     agree
     2     error
     3     developmental

In the main table, the same numbers are stored, translated by a combo box.

In the cross-tab query, use: 'Col' & StatusID as comlumn title, and add the column headings as usual.

1) For forms:
On form open, browse the table and rewrite the captions of the labels corresponding to each column/field. E.g.

    With CurrentDb.OpenRecordSet("tblStatusValues", dbOpenDynaset")
        Do Until .EOF
            Me("lblStatusValue" & .AbsolutePosition+1 = !StatusName
            If .AbsolutePostion >= 2 then exit Do
        Loop
    End With

2) For reports:
The same can be used, but if you don't want VB programming, there is another solution.
Create a simple cross-tab query on "tblStatusValues" showing a single row with the StatusName as value: First(StatusName).
Create a subform with that query *as* title area for your columns.
Use the other cross-tab to show the values.

Hope this helps!
0
 
ohgeeAuthor Commented:
Thanks Nico,

Presume Dim IntR is part of something else

>>Make sure that the number of Columns is not bigger as the number placed. The programcode has no protection against that !
Presume one can place fewer columns on the report than number of columns in the in query. Then make report columns invisible if there is no data? Also limit number of values that can be placed in the column field so that there is no possibity of there being too many columns for the report to handle. Is this right?

It is going to take me some time to try understand this so I might be quiet for a while.
0
 
ohgeeAuthor Commented:
Have also just seen Harfangs post so this will keep me busy too. Thanks for the help so far.
0
 
ohgeeAuthor Commented:
Ok, so Dim IntR must be for the control source
One thing I dont understand is   >> Me("Col" & intI - 1).ControlSource = rs.Fields(intI).Name
I also have three prelim columns, ie two rowheader columns and a totaL
Why the -1?   If the first IntI = 3 (column 4), then subtracting 1 would point to the Total column, would it not?
0
 
ohgeeAuthor Commented:
Or should it be -2?   If the first IntI is 3, then 3-2 = 1 which would make Col1    
Getting mixed up now
0
 
pique_techCommented:
Another related point, in order to use variable parameters in any crosstab query, you're required to specify the column headings--i.e., if you plan to get criteria from the user or from an open form.  

May not be directly relevant to this discussion as no one has mentioned variable parameters, but in case they're required, could be useful to know.
0
 
nico5038Commented:
Sorry for the confusion, but the intR isn't needed, just a remainder from the original complexer code.
The maximum number of fields is determined by the number of different values in the table and every new value will require a new column. I just coded this "straight away" for a situation where the maximum number of columns is known. To be safe you can always add extra (invisible because they won't be filled when there's no value) columns on a report to be flexible when new values are added, but the report space is limited...
You're correct about the -1 that should be -2 to get the result into Col1. Again an error from the "copy/paste devil"...

Best to implement the code and to see or the fields are filled as expected.

Nic;o)
0
 
ohgeeAuthor Commented:
Thank you for the feedback.
The report crosstab query is based on a parameter query and running >>Set rs = CurrentDb.OpenRecordset(Me.RecordSource) gives an error 3061, ie too few parameters, expected 7.
Is this what pique_tech is referring too? I would have hoped to get input boxes for setting the parameter values, but dont.
0
 
nico5038Commented:
A crosstable query is rather "picky" with parameters.
Personally I started using a regular query and base my crosstable query on that.
Best to fill that regular query with the needed values on forehand from code like:

dim QD as querydef

set qd=currentdb.querydefs("queryname")
qd.SQL = "select .... where x=" & me.x .... etc.

And after that run your crosstable query.

Nic;o)
0
 
ohgeeAuthor Commented:
I think that's what I have done Nic;o, but not sure if I understand this properly.  
There is an underlying query with parameters and the crosstab is based on this parameter query. >>Am I correct in understanding that this is what you suggest? It is set-up like this and the queries (select and crosstab) and report having been working fine for years now. It is an old database, but am now trying to create variable column headings so that I can empty the db and  give it to another user. But this new user might need different data values in the column heading field and then the report fails because the hard-coded values "agree", "error" and "developmental" wont work with the new values.

So with the code am now am stopped at >> Set rs = CurrentDb.OpenRecordset(Me.RecordSource)
It is looking for 7 parameters but does not give an input box so that I can enter them.
Thank you

0
 
nico5038Commented:
Guess we're running into a different problem here.
Without the SQL and table definitions it's hard to judge what's wrong.

Nic;o)
0
 
harfangCommented:
Maybe this helps... When you write:

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(Me.RecordSource)

You basically create an all new recordset using the form's Record Source property (a string). This is not the best way, and creates problems with parameters, it seems.

There are three better ways to obtain a matching record source object:

   set rs = Me.Recordset   ' identical recordset, with the same cursor
   set rs = Me.RecordsetClone   ' same, but with independant cursor
   set rs = Me.Recordset.Clone   '  same, but with completely private cursor

In this case, you are simply examining the .Fields collection and you do not need a cursor. Let me rewrite Nico's sample without the 'rs' variable:

    With Me.Recordset
        For intI = 3 To .Fields.Count - 1
            'Place headers
            Me("lblCol" & intI - 1).Caption = .Fields(intI).Name
            'Place correct controlsource
            Me("Col" & intI - 1).ControlSource = .Fields(intI).Name
        Next intI
    End With

Cheers ;)
0
 
ohgeeAuthor Commented:
Thanks Harfang
Still have a problem with >>  set rs = Me.Recordset
Get an error 2593, "This feature is not available in an MDB"

Am using Access 2003. The report is a subreport. The parameters work on the main report only. There are no parameters on the subreport.

Seems not right that this is not an easy thing to do. How can one distribute blank runtime solutions if people cannot choose their own values for column headings? Their reports wont work.
I do understand yours and Nico's solution re assigning values to the label and unbound text boxes so it seems current challenge is getting linked to the record source/set.
0
 
harfangCommented:
Yes, my mistake. There is no .Recordset or .RecordsetClone in reports.

Frankly, use my first suggestion. Do not use the actual content of the "Status Name" as column heading, but use some "Status Number", and translate that either as a subform or with simple DLookup functions.

If you want to go with your solution (examine the column heading of a complex query with parameters), you will have to store the SQL in a query and then go:

    With CurrentDb
        With .QueryDefs("qxtbNameOfYourQuery")
            .Parameters("First Param") = 123
            .Parameters("Second Param") = "I think so"
            ' etc
            With .OpenRecordset
                For intFN = 0 to .Fields.Count - 1
                    ' use .Fields(intFN).Name here...
                Next intFN
                .Close
            End With
        End With
    End With

If the column headings come from the recordsource of the subreport, this is not needed. You should be able to use (from the main report):

    With CurrentDb.OpenRecordset(Me.subReportControlName.Form.RecordSource)
        For intFN = 0 to .Fields.Count - 1
            ' use .Fields(intFN).Name here...
        Next intFN
        .Close
    End With

Good Luck
0
 
ohgeeAuthor Commented:
I cannot follow your instructions harfang, think my knowledge-level leaves too many gaps.

Is there not a way to populate the column headings with the values, when the values are created. The column headings are derived from a lookup table. Use a form to edit this table. Is it possible to use the form's AfterUpdate event, say, to get the values, properly formatted, into the pivot statement. The pivot statement in the crosstab goes thus, >> PIVOT BasisOfCrosstabsRequirements.RxAudit In ("Agree","Error","Developmental");
Is there a way to get the new values into this statement, by programming? Would have to open the query, called "CrosstabsRequirements" (my naming conventions need work) and insert the new values. Probably replace everything in the brackets, or maybe replace the whole pivot statement.  Would then have to get the control source of the text box, and the heading label, on the report to reflect the new value. Sounds too complicated, I guess.

Wonder why this feature has not been developed by Microsoft. Cant distribute a blank db because users will not be able to insert their own values. Thanks
0
 
nico5038Commented:
For user supplied values you should use a table.
In this case a table with e.g. the "agree", "error" and "developmental" values and re-used when filling the rows that are needed for your crosstable query.

Nic;o)
0
 
ohgeeAuthor Commented:
I do have a lookup table that holds these values, I am missing something here.
I will go away for a while and think about this.
Thanks Nic;o
0
 
nico5038Commented:
Just add a form where the user can maintain this table with these values that can be placed in the records and you're done :-)

Nic;o)
0
 
ohgeeAuthor Commented:
There is a lookup table with a form to add values, have always had this.
But how do the values get from there into the crosstab column headings without user input?  
0
 
nico5038Commented:
Only when they add rows in the table the crosstab is based on the crosstab will give the columns with the used value(s).

Using the relative fill code I provided the value won't be of any interest. It will be displayed in the header.

Best to try it in a test database:-)

Nic;o)
0
 
harfangCommented:
I think that in order to answer all your needs, we will have to go back to my suggestion of a lookup table (see http:#12691849).

Let me explain this again.

Instead of your fixed values "Agree","Error","Developmental", which you use throughout the code and the interface, create a separate table for these values. Let's say this is a "Status" and that the table will be called tlkpStatusCode. It can be a simple as (bytStatusID is the key):

    bytStatusID    strStatusName
           1            Agree
           2            Error
           3            Developmental

In the table(s) using the "Status", you will store just the status number. This is transparent to the user if you use a combo box every place you see that field. BTW, this works for reports as well.

Lets imagine a table with some stuff: tblSomeStuff, like this:

    lngStuffID   strStuffName    bytStatusID
            1        Junk                2
            2        Garbage          3
            3        Rubbish           3

Make sure you create a link in the relationship window between the fields bytStatusID of both tables, a so-called one-to-many relationship. This will ensure that only valid status codes can be entered and that status codes can no longer be deleted once used.

Now in your cross-tab query, do not use the Status Names, but just the Status Codes as in...

    PIVOT "Status" & BasisOfCrosstabsRequirements.bytStatusCode
    In ('Status1', 'Status2', 'Status3', 'Status4', 'Status5');

Likewise, you can obtain a row version of your Status table with:

    TRANSFORM First(strStatusName)
    SELECT False AS Dummy
    FROM tblStatusCode
    GROUP BY False
    PIVOT 'Status' & [bytStatusID] In ('Status1','Status2','Status3','Status4','Status5');

The dummy field is not used, you have only one row.

Both queries can now be used a source for a report. You can design the report in five columns, without header labels, and use a subreport showing just the tiny one-row cross-tab above to display the current value for the five column headings, which can be partially blank.

Another solution would be to use the code provided in the other comment to write the captions of the labels during the report's On Load event...

You can now change the values in the status codes table and have them immediately reflected in the report. You can also change the order of the columns (make sure you have selected "cascade update" in the relationship between the tables). Any number above 5 will not be shown in the report anymore, of course. But even this can be solved...

It's a little more complex, so try it later, but the idea is to change the name of column 5 to "(others)" when there are more than 5 status codes defined:

    TRANSFORM Last(IIf([bytStatusID]>5,'(other)',[strStatusName]))
    SELECT False AS Dummy
    FROM tblStatusCode
    GROUP BY False
    PIVOT 'Status' & IIf(bytStatusID > 5, 5, bytStatusID)
        In ('Status1','Status2','Status3','Status4','Status5');

This way, if the users do add a sith column, it will be perceived as a "limitation", not as a "bug".
Of course, you can also limit the status codes to be between 1 and 5 in the fields's validation rule to avoid the problem alltogether.


Now all this might seem rather complex, but it is the "normal" way to deal with variable column headings based on cross-tab data. The basic idea is that anything that can change needs to be stored in tables, not in code or SQL strings.

I hope this will work in your case...

Cheers :)
0
 
harfangCommented:
Just reread and found out that the values do come from a lookup table. It will be much easier to adapt in that case. The only question remaining is: in the main table, do you store the numbers or the names themselves?
0
 
ohgeeAuthor Commented:
I store the names. Lookup table has not had a number, used the names as primary key. Will have to add number field.
Thanks for last post. Will take a close look tonight.
0
 
harfangCommented:
If you do add a number, I suggest using Size: Byte (an integer between 0 and 255). You cannot have more than 255 columns anyway:)
If you want to limit the available column numbers, create a validation rule: Between 1 And 5, for instance.

If you need help to transform the field of the main table to a number with a combo box, feel free to ask. This would be an update query.
Another solution is just to open the table, remove the combo box definition if you have one, and use three times the search and replace in the field for the three possible values. Once it contains only the numbers 1, 2, and 3, you can change the data type to number. No need for a query in that case...

Good luck!
0
 
nico5038Commented:
I wouldn't use a number for the ID as it's easier to use just the value as you do now and save on a JOIN in the crosstable query.

Nic;o).
0
 
harfangCommented:
Nic: the whole point it to acheive stable column headings, regardless of future changes to the names. If you do not use numbers, you will still have to manage the variable column names of cross-tab queries with obscure code, not to mention sorting and special character issues. Using numbers as keys is fast, efficient, stable, and in this case required.
If you need a JOIN in the crosstab query to obtain the numbers, you are doing it the wrong way.

Cheers:)
0
 
nico5038Commented:
The whole point of the flexible columnheading code I provided is the fact that different values can be added "on the fly".
The storage of the values in a value table is only needed when you want to make sure that a fixed set of values is used during data-entry and to have the ability to change one value's description consistently in all rows where used.

Using a number instead of the desription will force you to "translate" these numbers before using them in the report header, thus the needed JOIN. Besides that I dislike coding descriptions as it makes the table with the foreign key harder to read in the testing phase.

Using your "stable" column headings will result in report changes when a value is added, where my flexible solution can have additional invisible columns that will become visible when needed.

Even sorting the columns is possible by adding an additional character or number in the description when you want them to appear other as by the ascending description value.

Nic;o)
0
 
ohgeeAuthor Commented:
Well, I think I got it. Dont know how but the headings are now flexible, ie change to reflect new data values in the column heading field. This is how it looks:
>>created number field, RxAuditID in the RxAudit lookup table. Made it autonumber and primary key.
>>linked this number field to its main table, Requirements
>>changed crosstab to read > PIVOT BasisOfCrosstabsRequirements.RxAuditID In ("1","2","3");
>>on report placed three text boxes in the appropriate header section and linked each one to one of the above numbers
>>in a higher header section, where the heading labels are, placed three text boxes and used a DLookup to link to the name value, eg =DLookUp("RxAudit","RxAudit","RxAuditID = 1")

And it seems to work. If a change is made to the name in the lookup table, then it reflects automatically in the audit forms. I think this is good. And of course the report seems to work, at this early testing stage. Presumably I can write some code that will keep the labels and data boxes invisible if there is no data.

If I might make a comment I think it was Harfang who pointed me to this solution. I'm afraid I dont know enough to argue merits of one way above another, although I will read the posts carefully to see what I can learn. But for sure I was pretty lost and the 30 Nov post got me to the current solution. Thanks very much Nic;o and Harfang.
I am going to award the points to Harfang.
0
 
harfangCommented:
ohgee: thanks! I'm glad you have now a solution that you master, this is important.
(nic: no hard feelings?)

Cheers:)
0
 
nico5038Commented:
No hardfeelings harfang, but just disappointed that the more complex and less flexible solution has been chosen.
Complex because you need for this solution to add a fixed number in a separate table and less flexible as the adding of a fieldvalue will always involve the change in the report design.
Even the removal of a number value from the table now will involve a report layout change as the DLOOKUP for the fixed number would otherwise fail.
The solution I proposed doesn't need such a number and allows for adding possible future values and isn't "sensitive" for value changes :)

Nic;o)
0
 
harfangCommented:
Hmm. That is not entirely fair. You have always a limit in the number of columns. In my suggestion, I created five (for three current values) as an example. You created "three, etc.". This is basically the same thing. In both solutions, a code change is required if the number of values increases beyond the number of controls present.

I also provided for an '(other)' column in case the number of values would increase beyond the current limit of the report(s), without any code change. This way, all data would still be printed, although with less details. Ohgee did not go this way, but it is feasible with only a change to the query. To achieve this in your code, you would have to build the totals "by hand" in the control source of the last column.

In both our solutions, if the maximal number of columns has to be changed, we need to add new controls and change the number of columns in various places. In both cases a change of data does not reaquire coding...

The removal of a number will simply produce a blank column. This seems logical. If the report has been designed to support a maximum of five columns, the user can use columns 1,2,3 or 2,3,4 or 1,3,5, as he wishes. In your case, the leftmost columns will always be blank.

The numbers can also be used to sort the columns in a meaningful order. In your suggestion, you would have to use a complex lookup scheme to reorder the columns (always alphabetical in the cross-tab query).

So, when you say "more complex and less flexible", I tend to agree with "complex" (but efficient), at least for the full solution suggested, but I respectfully dissagree with "less flexible".

The bottom line here is that I spent most of October designing several complex reports of this kind exactly. I ended up with this method and I like it. The users enjoy the fact that they can reorder the columns to their liking... This gives a good name to the key of the lookup table: it's just the column number!

Cheers:)
0
 
nico5038Commented:
I see you don't grasp the possibilities of my proposed solution.
When you want to put extra columns summed in a "overflow" column it's easy to add in the code the buildding of a string like "=a+b+c+..." that's to be placed in the field for the final column when constructing the header.

Sorting of the columns is easly achieved by using ascending description names like "1 - X", "2 - A", etc. and when you want this invisible then when filling the column header just use a MID() to remove these first four characters.

Having columns not "left aligned" is seen as bad design as it's harder to read a row with much white space.

Finally you'll have to make sure there's a number in the report in a DLOOKUP statement for each number value, I never have to check. My solution will even work without defining the additional number/description table.

Nic;o)
0
 
harfangCommented:
(The DLookup function will happily return Null when the number is not used, so this is not a problem)

But OK, I agree. Your solution can be put to use for any and all cross-tabs, whatever the source, while mine requires a specifc data structure. Thus yours can be considered a "more general" approach to the problem.

In any case, I enjoyed the debate. I have already learned a lot this way on EE :)
Cheers:)
0
 
nico5038Commented:
That's the real reward of EE :-)

C U

Nic;o)
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 13
  • 12
  • 11
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now