Solved

Reports from Crosstab Queries

Posted on 2001-06-13
15
317 Views
Last Modified: 2008-03-03
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?

0
Comment
Question by:Deebz
  • 7
  • 6
  • 2
15 Comments
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 

Author Comment

by:Deebz
Comment Utility
I hate to do this to you, but could you post an example of this...?  I have no access to the example mdb.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 200 total points
Comment Utility
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
 
LVL 2

Expert Comment

by:WonHop
Comment Utility
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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 
LVL 2

Expert Comment

by:WonHop
Comment Utility
Ok...thanks Jim  

Wonhop
0
 

Author Comment

by:Deebz
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:Deebz
Comment Utility
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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 

Author Comment

by:Deebz
Comment Utility
Jim...The report looks like it set up okay, but for some reason I'm only getting one record.  Any idea why?
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 

Author Comment

by:Deebz
Comment Utility
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
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
 

Author Comment

by:Deebz
Comment Utility
I'm getting only the first record of the set.  Still trying to figure out why I can't get more...
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Send along a MDB if you can...
Jim.

jimdettman@earthlink.net
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now