Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Reports from Crosstab Queries

Posted on 2001-06-13
15
Medium Priority
?
368 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 2
15 Comments
 
LVL 58
ID: 6186124
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
ID: 6186168
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 58

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 800 total points
ID: 6186294
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 2

Expert Comment

by:WonHop
ID: 6186571
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 58
ID: 6186592
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
ID: 6186632
Ok...thanks Jim  

Wonhop
0
 

Author Comment

by:Deebz
ID: 6187584
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
 

Author Comment

by:Deebz
ID: 6187664
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 58
ID: 6187724
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
ID: 6190525
Jim...The report looks like it set up okay, but for some reason I'm only getting one record.  Any idea why?
0
 
LVL 58
ID: 6190602
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
ID: 6194771
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 58
ID: 6194855
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
ID: 6195086
I'm getting only the first record of the set.  Still trying to figure out why I can't get more...
0
 
LVL 58
ID: 6195486
Send along a MDB if you can...
Jim.

jimdettman@earthlink.net
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

636 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