Dynamic Crosstab

[TaylorTot]

Hello,

I have successfully created a dynamic crosstab report in access which uses a crosstab query to populate my report.

Problem:
I need to add additional queries to this report.

Question:
Is it possible to create a dynamic crosstab from multiple recordsets?

Below is my current code for the Detail/Format section:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
   ' Put values in text boxes and hide unused text boxes.
    
   Dim intX As Integer
   '  Verify that you are not at end of recordset.
   If Not rstReport.EOF Then
      '  If FormatCount is 1, put 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("Comp" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
            Me("Can" + Format(intX)) = xtabCnulls(rstReport(intX - 1))
         Next intX
    
         '  Hide unused text boxes in the "Detail" section.
         For intX = intColumnCount + 2 To conTotalColumns
            Me("Comp" + Format(intX)).Visible = False
            Me("Can" + Format(intX)).Visible = False
         Next intX

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

Also, the code below sets up my recordset:


CODE
Private Sub Report_Open(Cancel As Integer)

   '  Create underlying recordset for report using criteria entered in
  
    
   Dim intX As Integer
   Dim qdf As QueryDef
   Dim frm As Form


   '  Set database variable to current database.
   Set dbsReport = CurrentDb
   Set frm = Forms!Monthly_Client_Individual_Frm
   '  Open QueryDef object.
   
   Set qdf = dbsReport.QueryDefs("Totals_Crosstab")
  
   ' Set parameters for query based on values entered
   qdf.Parameters(0) = Forms![Monthly_Client_Individual_Frm]![CustName_Cmb]
   
   '  Open Recordset object.
   Set rstReport = qdf.OpenRecordset()
      
  
   '  Set a variable to hold number of columns in crosstab query.
   intColumnCount = rstReport.Fields.Count
   
End Sub

Thanks in advance

 

[AlexCuse]

Have you tried dynamically building your SQL statement rather than using a saved query? I think that will be your best bet for this.

Hope it helps,

Alex

A wise man once said
"The only thing normal about database guys is their tables".

 

[TaylorTot]

Thank you for your reply. I have been setting up different queries and recordsets for every set of data. Unfortunately, as you can probably imagine this is a mess and isn't as efficient as I would hope.

Can you explain how to dynamically build my sql statement?

Again I appreciate your help!

 

[AlexCuse]

Basically, what you want to do is look at the sql view of your query. You are going to try to build a string that looks exactly like that. I think in your case it would involve a lot of if statements and the like. I have never done this with a crosstab but It will work the same.

Say your desired SQL statement is this:

select column1 from tablex

or this

select column2 from tabley

depending on the settings of controls on your form (x and y, 1 or 2)

We will call your form "TAYLORFORM" for the example.

to set up a dynamic sql statement based on these two controls (we'll call them table and field) you would use this code:

dim strSQL as string

strSQL = "select column" & forms("TAYLORFORM").controls("field").value & _
" from table" & forms("TAYLORFORM").controls("table").value

be extremely careful with your spaces and lack of spaces inside the quotes, as things like that will determine whether your queries work or not. You will find yourself doing a lot of

debug.print strSQL

if you are anything like me ;-)

You would then use strSQL in place of your query def when opening the recordset.

Good Luck,

Alex

A wise man once said
"The only thing normal about database guys is their tables".