Reports from Crosstab Queries

This is a question I've been struggling with for over a year.  The work-arounds I have in place are no longer working....

I have several crosstab queries where a batch date is designated as the Column Heading. The most current batch date is added and the oldest batchdate is removed daily.  
Is there any possible way a report can be created from this data in such a way that the fields on the report don't have to be renamed daily?

DeebzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Yes.  The sample solutions.MDB that comes with Access has an example of this.  I believe it's the employee sales report.

What it amounts to is that you fill the columns on the fly in the detail sections OnFormat event and the reports/group heading sections OnFormat event.

I can walk you through this step by step if need be.

Jim.

0
DeebzAuthor Commented:
I hate to do this to you, but could you post an example of this...?  I have no access to the example mdb.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Ok here's the way it works:

Your cross tab has some fixed columns, which are then followed by some variable columns (by "fixed" I mean that the column names don't change).  The fixed columns are no problem as you can base the report on the cross-tab and add controls, which are bound to those fields.  Bu for the variable ones, you need to fill unbound controls on the fly.

 You already have the report setup, so the only change you need to make is to have the controls for the variable column(s) unbound (set it's controlsource to blank).

Now in the reports declarations section add:

Option Compare Database 'Use database order for string comparisons.
Option Explicit

'  Constant for maximum number of columns query would
'  create.
Const conTotalColumns = 11

'  Variables for Database object and Recordset.
Dim dbsReport As Database
Dim rstReport As Recordset




Now in the reports open event, you'd do something like this:

Private Sub Report_Open(Cancel As Integer)
      '  Create underlying recordset for report.
   
    Dim intX As Integer
    Dim qdf As QueryDef
           
    '  Set database variable to current database.
    Set dbsReport = CurrentDb

    '  Open QueryDef object.
    Set qdf = dbsReport.QueryDefs("myCrossTab")

    ' Set parameters for query based on values entered
    ' in frmSelectReport.
    ' qdf.Parameters(<parameter name>) = <some value>

    '  Open Recordset object.
    Set rstReport = qdf.OpenRecordset()
   
    '  Set a variable to hold number of columns in crosstab query.
    intColumnCount = rstReport.Fields.Count
   
End Sub


This makes the cross-tab results available to us in a recordset.  Next we need to take care of the headings so you would do something like this:
 Private Sub PageHeader_Format(Cancel As Integer, FormatCount As Integer)
       Dim intX As Integer
   
       
    '  Put column headings into text boxes in page header.
    For intX = 1 To intColumnCount
        Me("Head" + Format(intX)) = rstReport(intX - 1).Name
 
    Next intX

    '  Hide unused text boxes in page header.
    For intX = (intColumnCount + 1) To conTotalColumns
        Me("Head" + Format(intX)).Visible = False
   Next intX
End Sub


  What we done here is to have a set of unbound column heading controls named Head1, Head2, Head3, Head4...etc and filled them with the heading names provided by the query.

  Next we need to fill in the actual detail records so:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    '  Place values in text boxes and hide unused text boxes.
   

    Dim intX As Integer
   
    '  Verify that not at end of recordset.
    If Not rstReport.EOF Then
        '  If FormatCount is 1, place values from recordset into text boxes
        '  in detail section.
        If Me.FormatCount = 1 Then
            For intX = 1 To intColumnCount
                '  Convert Null values to 0.
                Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
            Next intX
   
            '  Hide unused text boxes in detail section.
            For intX = intColumnCount + 1 To conTotalColumns
                Me("Col" + Format(intX)).Visible = False
            Next intX

            '  Move to next record in recordset.
            rstReport.MoveNext
        End If
    End If
End Sub

  Same idea as the headings, but here the unbound controls are named Col1, Col2, Col3, etc.  We also make sure that we advance to the next record here.

  and last, some cleanup:

Private Sub Detail_Retreat()
    ' Always back up to previous record when detail section retreats.
    rstReport.MovePrevious
End Sub

Private Sub Report_Close()
       On Error Resume Next

    '  Close recordset.
    rstReport.Close
   
End Sub

  Looks complicated, but take it one step at a time like we did here.

 Let me know if you need anything else.
Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

WonHopCommented:
Hello Deeze and Jim

Jim, please correct me if I am wrong.  I believe if you have Access on your machine, you should have the sample database.  
Mine is locate here: C:\Program Files\Microsoft Office\Office\Samples\Solutions.mbd.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
WonHop,

  It's not installed by default.  It may or may not be there.  But if it's not, the install can be rerun.

Jim.
0
WonHopCommented:
Ok...thanks Jim  

Wonhop
0
DeebzAuthor Commented:
Jim...

just wanted to let you know that I'm meeting-impared today & won't be able to work with your code until tomorrow.  Thanks for helping!  I'll test it ASAP.

(btw...this is a company owned Access & if samples were installed, they were probably put on a one of those special out-of-the-way servers that only show up on the Network on the 5th Saturday of the 13th month.)

D
0
DeebzAuthor Commented:
Jim...

I got a variable not defined for intColumnCount & dimmed it as an integer in report declarations (was this okay?)

Also, the code stops at Sub or Function not defined (("xtabCnulls")).  This was used in the Sub Detail Format as

Me("Col" + Format(intX)) = xtabCnulls(rstReport(intX - 1))

I'm not sure what to do with that to get farther into the code...
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Sorry.  

Yes intColumnCout should be an integer.

The xTabCNulls is just a null to zero function.  You can use NZ() in it's place or drop it.

Also be aware that I adjusted the code to eliminate the totals column that was in the sample, so I might be off a bit.  Give it a whirl and let me know.

Jim.
0
DeebzAuthor Commented:
Jim...The report looks like it set up okay, but for some reason I'm only getting one record.  Any idea why?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
First, make sure that the query your using is returning more then one record and that the report is using it for its data source.

Then check and make sure that your doing this:

 '  Move to next record in recordset.
 rstReport.MoveNext

in the detail section's OnFormat event.

Jim.
0
DeebzAuthor Commented:
my record source is okay and I'm using the correct data source.  and I do have rstReport.MoveNext in the detail section's OnFormat event.  I also took out all the sorts, but that didn't help.  Could it have something to do with this line:

Me("Col" + Format(intX)) = Nz(rstReport(intX - 1))

This is the line that I replaced the xtabCnulls with the Nz function.

Thanks again :)

Deb
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I guess it depends what you mean by "only one record".  Same record repeated over and over or simply one detail line?

 But in either case, the NZ would not make a difference.  Below is the original function and as you can see, it simply sets the value to a 0 if the field is null, otherwise it gives you the field value.

Jim

Private Function xtabCnulls(varX As Variant)

    ' Test if a value is null.
    If IsNull(varX) Then
        ' If varX is null, set varX to 0.
        xtabCnulls = 0
    Else
        ' Otherwise, return varX.
        xtabCnulls = varX
    End If

End Function
0
DeebzAuthor Commented:
I'm getting only the first record of the set.  Still trying to figure out why I can't get more...
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Send along a MDB if you can...
Jim.

jimdettman@earthlink.net
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.