Report based on crosstab query errors when column missing.

[KMITCH]

I have a report based on a crosstab query.<br>The report has calculations for row and column sums.<br>The report expects all of the columns which were there<br>when it was originally created, therefore errors out<br>when one of those columns is missing in a subsequent<br>run due to the fact that there were no rows with that<br>value in the current period.<br><br>Can anyone give me some ideas for workarounds.<br><br>My only idea, so far, is to have the report 1)create a <br>temporary table each time it runs, 2)load table using VBA<br>and SQL 3) report 4) drop the table.&nbsp;&nbsp;This would probably<br>work but seems somewhat cumbersome.<br><br>All ideas appreciated.<br><br>Thanks,<br>KMITCH

 

[rbowes]

Try this: Go into the design of the crosstab query and click on the gray area above the grid(be sure not to click on a source query/table). The query properties dialog box will open, the second line is called &quot;Column Headings&quot;, on this line enter in the column headings that you will be using in the order that you want.&nbsp;&nbsp;Be sure that you type the column names exactly as they would appear if you just ran the query without inputing these headings.&nbsp;&nbsp;By establishing the headings for the crosstab you are making them standard for the query and will be used in when making a form or a report - if no data exists for a column, it will be blank.&nbsp;&nbsp;On the report, set your report footer controls(where you total a column) to <br>&quot;control source = Sum([My Column Heading])&quot; and this will do the trick.<br><br>Good Luck!<br><br>Bob B.

 

[elizabeth]

There is a wonderful but lengthy solution detailed in Alison Balter's Mastering Access 97 in a chapter called &quot;Reports: Advanced Techniques&quot; page 440 called &quot;Building a Report from a Crosstab Query&quot; that builds columns dynamically. Not only does it build the column header at run time, it allows you to define as many columns as fit on the page and hide any unused columns. I used this for a crosstab with Project Managers as a Column Header, as Project Managers come and go frequently. AB has a new book out for version 2000 but haven't bought it yet so don't know if it's in there too.

 

[KMITCH]

Thank you Elizabeth and rbowes.&nbsp;&nbsp;I can try rbowes'<br>solution in the short run.&nbsp;&nbsp;Elizabeth's solution <br>gives me the excuse I need to run out and buy <br>Mastering Access 97.<br><br>Elizabeth, will your solution still allow for row<br>and column totals in the report?<br><br>Thanks,<br>KMITCH

 

[elizabeth]

Mine only has column totals, and a report grand total, but, glancing at the solution, it looks like it wouldn't be too much of a stretch to figure out how & where to insert the row totals.

 

[DreewToo]

Thank you RBowes, I've been trying to figure out how ot name the columns after running the crosstab query report, and only after reading your response found out how.

 

[Telsa]

umm.. know I'm late in this game... but here's what I do for the months of my crosstab...

In the properties of the Cross Tab query, there is a field called Column Headings... and keyed in &quot;Jan&quot;, &quot;Feb&quot;, etc..

and then I created a Field for my date that formats using the Column Headings

Month: Format([Period], &quot;mmm&quot

Was wondering if this might have been helpful to you (I know, after the fact).

Mary

 

[PaulF]

This solution is based on expecting the same columns each report, therefore the report has a textbox for each expected field. When the report opens, it loops through the fields and sets them into an array. It then loops through the textboxes on the report and checks the control source for them. If it doesn't find the controlsource in the arrary, it sets the controlsource for that textbox to &quot;= 0&quot; (or you could use VbNullString), otherwise it leaves it alone. This only works for Crosstabs where there are not going to be new field names that don't exist already in the report.

Private Sub Report_Open(Cancel As Integer)
Dim db As DATABASE, rst As Recordset, qdf As QueryDef, fld As Field
Dim x As Integer, strFldName() As String
x = 0
Set db = CurrentDb
For Each qdf In db.QueryDefs
If qdf.name = &quot;YourCrosstabQueryName&quot; Then
x = qdf.Fields.Count

ReDim strFldName(1 To x) As String
x = 0
For Each fld In qdf.Fields
x = x + 1
strFldName(x) = fld.name
Next
End If
Next qdf
Dim strMsg As String, i As Integer, strZero As String
strZero = 0
Dim ctl As Control, blnHaveField As Boolean
For Each ctl In Me.Controls
blnHaveField = False
If TypeOf ctl Is TextBox Then
For i = 1 To x
If ctl.ControlSource = strFldName(i) Then
blnHaveField = True
End If
Next i
If blnHaveField = False Then ctl.ControlSource = &quot;= 0&quot; 'vbNullString


End If
Next ctl
Exit Sub
End Sub


PaulF

 

[MichaelRed]

A very different approach. Create a Query which does have all of the (desired) column headings (it doesn't need any data). I another new query, left join the columnheading query with the crosstab query, Forces all of the columns to appear! May appear to be more work, but it has the advantage of being very flexible.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Jackie]

I had this same problem. Michael's solution worked best as I new the possible column headings.

Thank you Michael!

-jackie