Dynamic Crosstab report (1 Viewer)

[jules68]

Hello! I have spent hours researching and trying different code, to no avail. Now I need to ask for some help.

I have a report which runs off a crosstab query. Basically I have 2 row headings, Metric and Centre. I have 13 column headings, these are for data covering a period of 4 weeks (instead of a month), we call these “SP” (currently at this point in time, I have data for SP10 (2012), SP11 (2012), SP12 (2012), SP13(2012), then we go to SP01(2013), SP02(2013) etc up to SP09(2013). (I also have a reference table set, but not used within this query) the value of the crosstab is based on a count of “Log On” (meaning staff id).

My crosstab runs perfectly. I can also hard code the report to run perfectly as well. The only problem is each SP (each 4 weeks) I am having to manually change all of the 13 SP labels, 13 SP text boxes (to change the data source) and the 13 totals I have at the base of each column.

I’ve only been learning Access and VBA for about 6 months, I’ve struggled to understand a lot of the code I’ve found online.

From what I’ve read I should be able to put some code into the OnLoad or On Open event in my report.

Here is the SQL from my crosstab query.

TRANSFORM Count(qryRAWDATAByMetric.[Log On]) AS [CountOfLog On]
SELECT qryRAWDATAByMetric.Metric, qryRAWDATAByMetric.CentreInstanceOccured AS Centre
FROM qryRAWDATAByMetric RIGHT JOIN qryCalcMetricTotalLast3SPs ON (qryRAWDATAByMetric.CentreInstanceOccured = qryCalcMetricTotalLast3SPs.Centre) AND (qryRAWDATAByMetric.Metric = qryCalcMetricTotalLast3SPs.Metric)
GROUP BY qryRAWDATAByMetric.Metric, qryRAWDATAByMetric.CentreInstanceOccured
PIVOT qryRAWDATAByMetric.SP;

I have named the 13 labels as lblSP1, lblSP2 etc up to lblSP13.
I have named my text boxes as txtSP1, txtSP2 etc up to txtSP13.
And again with my totals text boxes TotalsSP1, etc.

I’m just not sure how to write the code to make this dynamic.


As my database has staff info in it, i'm not able to upload this. If required i can recreate a sample of the data without personal details if that would help. I'm hoping ther is some general code that i can use. I'm only a beginning with VBA but i'm starting to get a good understanding.

Any assistance would be greatly appreciated.

 

[JHB]

As my database has staff info in it, i'm not able to upload this. If required i can recreate a sample of the data without personal details if that would help. I'm hoping ther is some general code that i can use. I'm only a beginning with VBA but i'm starting to get a good understanding.

Then zip your database and post it, it should be more as 1 month data too see if the solution will run.

 

[Mr. B]

jules68,

I had issues dealing with the same thing the first time I ran into this problem. For those that do not know about this issue, the problem comes from the fact that a crosstab query produces specific column names and a report has no way of knowing what those column names are going to be.

I'm only a beginning with VBA but i'm starting to get a good understanding.

I hope you are good at understanding code, because this gets a little involved. The general concept is that you use VBA code and have it use a a query or sql statement that will return the column names that will be being produced by the crosstab query. The values returned by this query or sql statement are then use to create a comma delimited string of the values. These values are then used as the column names. This is done by opening your report in design mode and assigning the column names along with modifying the labels for each column.

Below is the code that I used to do this: Note that there are a could of Functions used here. Also note that there were multiple reports with three options for adding "Sparklines" and adding a calculated percent difference for month over month comparison so the code is handling more than just the labels and field names for the report.

Here is the code that was in the On Cllick event of a command button:

Private Sub cmdOutputReport_Click()

'DEVELOPER'S NOTES:
'   In order to produce the report using a crosstab type query, code has been developed that
'   will determine the field names that will be created by the crosstab query that produces
'   the data for the report and then modify the appropriate report, assigning the appropriate
'   field names to the appropriate controls, making those controls visible and able to be
'   populated based on the report selected in the "grpReportType" options group and if the
'   "chkPcntChg" checkbox is checked

'reset the "strFldList" variable and the "cntr" and "varChrLoc" variables
strFldList = ""
cntr = 1
varChrLoc = 1

Select Case Me.grpReportType
    Case 1  'Specify the report that is to be prepared
        'get the list of fields expected
        strFldList = GetListOfFieldNames("All RUs")
        'assign the report name to the variable "strRptName"
        If Me.chkPcntChg = True Then
            If Me.chkTrendLines = True Then
                strRptName = "rptTotalUsePerMonthByRU-All-PercentChg-WithSpark"
            Else
                strRptName = "rptTotalUsePerMonthByRU-All-PercentChg"
            End If
        Else
            strRptName = "rptTotalUsePerMonthByRU-All"
        End If
        'open the report in design view that is to have the field names (controls source) assigned
        DoCmd.OpenReport (strRptName), acViewDesign
ReadFieldNamesAllRpt:
        varNxtChrLoc = InStr(varChrLoc, strFldList, ",")
        'read the Field name
        If varNxtChrLoc > 0 Then
            strFldNames(cntr) = Mid(strFldList, varChrLoc, varNxtChrLoc - varChrLoc)
        Else
            strFldNames(cntr) = Mid(strFldList, varChrLoc, Len(strFldList))
        End If
        'assign the Control Source of the field control
        With Reports(strRptName).Controls("txtFld" & cntr)
            .ControlSource = strFldNames(cntr)
            .Visible = True
        End With
        'set the caption for the label
        With Reports(strRptName).Controls("lblFld" & cntr)
            .Caption = strFldNames(cntr)
            .Visible = True
        End With
        
        varChrLoc = varNxtChrLoc + 1
        
        If varNxtChrLoc > 0 Then
            cntr = cntr + 1
            GoTo ReadFieldNamesAllRpt
        End If
        
        'Assign the query name to be used for exporting to Excel to the variable "strExportQueryName"
        'Note: this is the same query that is used to produce the data for the report
        strExportQueryName = "qryTotalUsePerMonthByRU_Crosstab"
        
    Case 2  'configure the "rptTotalUsePerMonthByRU-Corp" or the "rptTotalUsePerMonthByRU-Corp-PercentChg" report
        'get the list of fields expected
        strFldList = GetListOfFieldNames("Only Corp")
        'assign the report name to the variable "strRptName"
        If Me.chkPcntChg = True Then
            If Me.chkTrendLines = True Then
                strRptName = "rptTotalUsePerMonthByRU-Corp-PercentChg-WithSpark"
            Else
                strRptName = "rptTotalUsePerMonthByRU-Corp-PercentChg"
            End If
        Else
            strRptName = "rptTotalUsePerMonthByRU-Corp"
        End If
        'open the report in design view that is to have the field names (controls source) assigned
        DoCmd.OpenReport (strRptName), acViewDesign
ReadFieldNamesCorp:
        varNxtChrLoc = InStr(varChrLoc, strFldList, ",")
        'read the Field name
        If varNxtChrLoc > 0 Then
            strFldNames(cntr) = Mid(strFldList, varChrLoc, varNxtChrLoc - varChrLoc)
        Else
            strFldNames(cntr) = Mid(strFldList, varChrLoc, Len(strFldList))
        End If
        'assign the Control Source of the first field control
        With Reports(strRptName).Controls("txtFld" & cntr)
            .ControlSource = strFldNames(cntr)
            .Visible = True
        End With
        'set the caption for the first field
        With Reports(strRptName).Controls("lblFld" & cntr)
            .Caption = strFldNames(cntr)
            .Visible = True
        End With
        
        varChrLoc = varNxtChrLoc + 1
        If varNxtChrLoc > 0 Then
            cntr = cntr + 1
            GoTo ReadFieldNamesCorp
        End If
        'Assign the query name to be used for exporting to Excel to the variable "strExportQueryName"
        'Note: this is the same query that is used to produce the data for the report
        strExportQueryName = "qryTotalUsePerMonthByRU-Corp_Crosstab"
        
    Case 3  'configure the "rptTotalUsePerMonthByRU-Retail" or the "rptTotalUsePerMonthByRU-Retail-PercentChg" report
        'get the list of fields expected
        strFldList = GetListOfFieldNames("Only Retail")
        'assign the report name to the variable "strRptName"
        If Me.chkPcntChg = True Then
            If Me.chkTrendLines = True Then
                strRptName = "rptTotalUsePerMonthByRU-Retail-PercentChg-WithSpark"
            Else
                strRptName = "rptTotalUsePerMonthByRU-Retail-PercentChg"
            End If
        Else
            strRptName = "rptTotalUsePerMonthByRU-Retail"
        End If
        'open the report in design view that is to have the field names (controls source) assigned
        DoCmd.OpenReport (strRptName), acViewDesign
ReadFieldNamesAllRetail:
        varNxtChrLoc = InStr(varChrLoc, strFldList, ",")
        'read the Field name
        If varNxtChrLoc > 0 Then
            strFldNames(cntr) = Mid(strFldList, varChrLoc, varNxtChrLoc - varChrLoc)
        Else
            strFldNames(cntr) = Mid(strFldList, varChrLoc, Len(strFldList))
        End If
        'assign the Control Source of the first field control
        With Reports(strRptName).Controls("txtFld" & cntr)
            .ControlSource = strFldNames(cntr)
            .Visible = True
        End With
        'set the caption for the first field
        With Reports(strRptName).Controls("lblFld" & cntr)
            .Caption = strFldNames(cntr)
            .Visible = True
        End With
        
        varChrLoc = varNxtChrLoc + 1
        If varNxtChrLoc > 0 Then
            cntr = cntr + 1
            GoTo ReadFieldNamesAllRetail
        End If
        'Assign the query name to be used for exporting to Excel to the variable "strExportQueryName"
        'Note: this is the same query that is used to produce the data for the report
        strExportQueryName = "qryTotalUsePerMonthByRU-Retail_Crosstab"
End Select

'assign the last reporting Month and Year to the variable "strRptMoYr"
'this value will be used in the naming of the Excel and PDF output files
strRptMoYr = Replace(strFldNames(cntr), "/", "-")
'position the "Total RU Usage" label and "txtSumOfUsage" text box controls
'and the "Spark" label control based on
'the number of months being reported
Select Case cntr
    Case 1
        Reports(strRptName).Controls("txtSumOfUsage").Left = 3359.952
        Reports(strRptName).Controls("lblTotalUse").Left = 3359.952
        If Me.chkTrendLines = True Then
            Reports(strRptName).Controls("Spark").Left = 4560.048
        End If
    Case 2
        Reports(strRptName).Controls("txtSumOfUsage").Left = 4320
        Reports(strRptName).Controls("lblTotalUse").Left = 4320
        If Me.chkTrendLines = True Then
            Reports(strRptName).Controls("Spark").Left = 5519.952
        End If
    Case 3
        Reports(strRptName).Controls("txtSumOfUsage").Left = 5280.048
        Reports(strRptName).Controls("lblTotalUse").Left = 5280.048
        If Me.chkTrendLines = True Then
            Reports(strRptName).Controls("Spark").Left = 6480
        End If
    Case 4
        Reports(strRptName).Controls("txtSumOfUsage").Left = 6239.952
        Reports(strRptName).Controls("lblTotalUse").Left = 6239.952
        If Me.chkTrendLines = True Then
            Reports(strRptName).Controls("Spark").Left = 7440.048
        End If
    Case 5
        Reports(strRptName).Controls("txtSumOfUsage").Left = 7200
        Reports(strRptName).Controls("lblTotalUse").Left = 7200
        If Me.chkTrendLines = True Then
            Reports(strRptName).Controls("Spark").Left = 8399.952
        End If
    Case 6
        Reports(strRptName).Controls("txtSumOfUsage").Left = 8160.048
        Reports(strRptName).Controls("lblTotalUse").Left = 8160.048
        If Me.chkTrendLines = True Then
            Reports(strRptName).Controls("Spark").Left = 9360
        End If
    Case 7
        Reports(strRptName).Controls("txtSumOfUsage").Left = 9119.952
        Reports(strRptName).Controls("lblTotalUse").Left = 9119.952
        If Me.chkTrendLines = True Then
            Reports(strRptName).Controls("Spark").Left = 10320.048
        End If
    Case 8
        Reports(strRptName).Controls("txtSumOfUsage").Left = 10080
        Reports(strRptName).Controls("lblTotalUse").Left = 10080
        If Me.chkTrendLines = True Then
            Reports(strRptName).Controls("Spark").Left = 11279.952
        End If
    Case 9
        Reports(strRptName).Controls("txtSumOfUsage").Left = 11040.048
        Reports(strRptName).Controls("lblTotalUse").Left = 11040.048
        If Me.chkTrendLines = True Then
            Reports(strRptName).Controls("Spark").Left = 12240
        End If
    Case 10
        Reports(strRptName).Controls("txtSumOfUsage").Left = 11999.952
        Reports(strRptName).Controls("lblTotalUse").Left = 11999.952
        If Me.chkTrendLines = True Then
            Reports(strRptName).Controls("Spark").Left = 13200.048
        End If
    Case 11
        Reports(strRptName).Controls("txtSumOfUsage").Left = 12960
        Reports(strRptName).Controls("lblTotalUse").Left = 12960
        If Me.chkTrendLines = True Then
            Reports(strRptName).Controls("Spark").Left = 14159.952
        End If
    Case 12
        Reports(strRptName).Controls("txtSumOfUsage").Left = 13920.048
        Reports(strRptName).Controls("lblTotalUse").Left = 13920.048
        If Me.chkTrendLines = True Then
            Reports(strRptName).Controls("Spark").Left = 15120
        End If
End Select
'close and save the changes to the report
DoCmd.Close acReport, strRptName, acSaveYes

'read the output path from the "tblAppInfo" table
strOutputPath = DLookup("OutputPath", "tblPersonalAppSettings")

Select Case Me.grpOutputOpts
    Case 1  'Preview Report
        DoCmd.OpenReport strRptName, acViewPreview
        DoCmd.Maximize
        If Me.chkTrendLines = True Then
            DoCmd.RunCommand acCmdZoom75
        Else
            DoCmd.RunCommand acCmdZoom100
        End If
    Case 2  'Send to printer
        DoCmd.OpenReport strRptName, acViewNormal
    Case 3  'Export to Excel
        'check to be sure that the path to the "output" location exists
        If fIsFileDIR(strOutputName, vbDirectory) = -1 Then
            strMsg = "The folder currently defined as the ""Output"" loacation where the Excel files " _
                   & "should be save to could not be found." & vbNewLine & vbNewLine & "Please update " _
                   & "this path using the ""Manage file Paths"" option from the ""Admin Options Menu""."
            MsgBox strMsg, vbCritical + vbOKOnly, "Folder for Output Files Not Found"
            Exit Sub
        End If
        'assign only the name for the output file - the extension will be added in the "Case" statement
        strOutputName = strRptMoYr & "-" & strExportQueryName
        DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, strExportQueryName, strOutputPath & "\" & strOutputName & ".xlsx"
        MsgBox "Data successfully exported to Excel." _
               & vbNewLine & "The file can be found at:" _
               & vbNewLine & vbNewLine & strOutputPath & "\" & strOutputName & ".xlsx"
    Case 4  'creat PDF
        'check to be sure that the path to the "output" location exists
        If fIsFileDIR(strOutputName, vbDirectory) = -1 Then
            strMsg = "The folder currently defined as the ""Output"" loacation where the PDF documents " _
                   & "should be save to could not be found." & vbNewLine & vbNewLine & "Please update " _
                   & "this path using the ""Manage file Paths"" option from the ""Admin Options Menu""."
            MsgBox strMsg, vbCritical + vbOKOnly, "Folder for Output Files Not Found"
            Exit Sub
        End If
        strOutputName = strRptMoYr & "-" & Right(strRptName, Len(strRptName) - 3)
        DoCmd.OutputTo acOutputReport, strRptName, acFormatPDF, strOutputPath & "\" & strOutputName & ".pdf", False
        MsgBox "PDF document created successfully." _
               & vbNewLine & "The file can be found at:" _
               & vbNewLine & vbNewLine & strOutputPath & "\" & strOutputName & ".pdf"""
End Select
DoCmd.Echo True
DoCmd.Hourglass False
End Sub

Public Function GetListOfFieldNames(FilterType As String) As String
'the "FilterType" values expected would be the value of the Select Case statement below
'get the filter criteria for the rolling 12 months
strMaxMoYr = GetMaxMoYr
strStartMoYr = GetMoYrForRolling12MonthCriteria

Select Case FilterType
    Case "All RUs"
        'get the list of Month and Year values for only All LOBs
        strSql = "SELECT DISTINCT tblTmpReportData.MoYr, tblTmpReportData.DataYear, " _
               & "tblTmpReportData.DataMonth FROM tblTmpReportData " _
               & "WHERE (((tblTmpReportData.MoYr) >= '" & strStartMoYr & "' " _
               & "Or (tblTmpReportData.MoYr) <= '" & strMaxMoYr & "')) " _
               & "ORDER BY tblTmpReportData.DataYear, tblTmpReportData.DataMonth;"
        
    Case "Only Corp"
        'get the list of Month and Year values for only Corp LOB
        strSql = "SELECT DISTINCT tblTmpReportData.MoYr, tblTmpReportData.DataYear, " _
               & "tblTmpReportData.DataMonth FROM tblTmpReportData " _
               & "WHERE (((tblTmpReportData.MoYr)>='" & strStartMoYr & "' " _
               & "Or (tblTmpReportData.MoYr)<='" & strMaxMoYr & "') " _
               & "AND ((tblTmpReportData.LineOfBusinessID)=1)) " _
               & "ORDER BY tblTmpReportData.DataYear, tblTmpReportData.DataMonth;"
    
    Case "Only Retail"
        'get the list of Month and Year values for only Retail LOB
        strSql = "SELECT DISTINCT tblTmpReportData.MoYr, tblTmpReportData.DataYear, " _
               & "tblTmpReportData.DataMonth FROM tblTmpReportData " _
               & "WHERE (((tblTmpReportData.MoYr)>='" & strStartMoYr & "' " _
               & "Or (tblTmpReportData.MoYr)<='" & strMaxMoYr & "') " _
               & "AND ((tblTmpReportData.LineOfBusinessID)=5)) " _
               & "ORDER BY tblTmpReportData.DataYear, tblTmpReportData.DataMonth;"
    Case "All Servers"
        'get the list of Month and Year values for top 25 percent change in All server tape
        strSql = "SELECT DISTINCT [tblTapeServerDetails]![DataMonth] & ""/"" & " _
               & "[tblTapeServerDetails]![DataYear] AS MoYr, tblTapeServerDetails.DataYear, " _
               & "tblTapeServerDetails.DataMonth FROM qrySvrTapePercentChangeOver12Months " _
               & "INNER JOIN (tblTapeServers INNER JOIN tblTapeServerDetails ON " _
               & "tblTapeServers.TapeServerID = tblTapeServerDetails.TapeServerID) ON " _
               & "qrySvrTapePercentChangeOver12Months.Server = tblTapeServers.Server " _
               & "ORDER BY tblTapeServerDetails.DataYear DESC , tblTapeServerDetails.DataMonth;"
    Case "Only Corp Servers"
        'get the list of Month and Year values for top 25 percent change in Only Corp server tape
        strSql = "SELECT DISTINCT [tblTapeServerDetails]![DataMonth] & ""/"" & " _
               & "[tblTapeServerDetails]![DataYear] AS MoYr, tblTapeServerDetails.DataYear, " _
               & "tblTapeServerDetails.DataMonth FROM qrySvrTapePercentChangeOver12Months " _
               & "INNER JOIN (tblTapeServers INNER JOIN tblTapeServerDetails ON " _
               & "tblTapeServers.TapeServerID = tblTapeServerDetails.TapeServerID) ON " _
               & "qrySvrTapePercentChangeOver12Months.Server = tblTapeServers.Server " _
               & "WHERE (((tblTapeServerDetails.LineOfBusinessID) = 1)) " _
               & "ORDER BY tblTapeServerDetails.DataYear DESC , tblTapeServerDetails.DataMonth;"
    Case "Only Retail Servers"
        'get the list of Month and Year values for top 25 percent change in Only Retail server tape
        strSql = "SELECT DISTINCT [tblTapeServerDetails]![DataMonth] & ""/"" & " _
               & "[tblTapeServerDetails]![DataYear] AS MoYr, tblTapeServerDetails.DataYear, " _
               & "tblTapeServerDetails.DataMonth FROM qrySvrTapePercentChangeOver12Months " _
               & "INNER JOIN (tblTapeServers INNER JOIN tblTapeServerDetails ON " _
               & "tblTapeServers.TapeServerID = tblTapeServerDetails.TapeServerID) ON " _
               & "qrySvrTapePercentChangeOver12Months.Server = tblTapeServers.Server " _
               & "WHERE (((tblTapeServerDetails.LineOfBusinessID) = 5)) " _
               & "ORDER BY tblTapeServerDetails.DataYear DESC , tblTapeServerDetails.DataMonth;"
End Select
'reset the "strColNames" variable
strColNames = ""
Set rs = CurrentDb.OpenRecordset(strSql)
rs.MoveLast
rs.MoveFirst
varRecCnt = rs.RecordCount
For varRowCntr = 1 To varRecCnt
    'read the month and Year value to a string
    If strColNames = "" Then
        strColNames = rs.Fields("MoYr").value
    Else
        strColNames = strColNames & "," & rs.Fields("MoYr").value
    End If
    rs.MoveNext
Next varRowCntr
GetListOfFieldNames = strColNames

End Function

I hope this provide you with some help.

 

[jules68]

Thanks Mr.B, i'll give your code a try this week, i'll get back to you soon. Much appreciated.

 

[ChrisO]

If your query had an IN list such as:-

TRANSFORM First(tblJobs.Job) AS JobDesc
SELECT tblJobs.JobDate
FROM tblJobs
WHERE (((tblJobs.JobDate) Between #1/1/2013# And #3/31/2013#))
GROUP BY tblJobs.JobDate
ORDER BY tblJobs.JobDate
PIVOT tblJobs.Person In ("Bob Hawke","Cathy Freeman","Charles Perkins","Gough Whitlam","Idi Amin","Julia Gillard","Kerry O'Brien","Kevin Rudd","Nelson Mandela");

And if that IN list was generated by a Public Function such as:-

Public Function PersonList() As String
    Dim lngCount  As Long
    Dim strSQL    As String
    Dim strPeople As String
    
    strSQL = " SELECT [FirstName] & ' ' & [LastName] AS Person" & _
             " FROM tblEmployees" & _
             " WHERE [DisplayInCrosstab] = True" & _
             " ORDER BY [FirstName] & ' ' & [LastName]"
             
    With CurrentDb.OpenRecordset(strSQL, CON.DAO.dbOpenDynaset)
        Do Until .EOF
            lngCount = lngCount + 1
            If lngCount > CON.JOBS.MaxPeople Then Exit Do
        
            strPeople = strPeople & Chr(34) & !Person & Chr(34) & ","
            .MoveNext
        Loop
    End With
    
    If Right(strPeople, 1) = "," Then
        PersonList = Left(strPeople, Len(strPeople) - 1)
    End If
    
End Function

Then that IN list can also be used for the Report labels and control sources.
Also, there is no need to go into Report design mode to make the changers so it can be done in an MDE database as well.

An Access 2003 demo is attached.

Chris.

 

[jules68]

Thank you for your responses. I'm still not having any luck.

I have made some changes in my database to allow me to post it. Mr.B and ChrisO your both on the right track of what I'm after.

I have 2 issues i would like to resolve.

Firstly to make my report dynamic. Ok just to clarify what i'm after.

On frmSelectMetric, I have 2 buttons that are linked to the same report [rpt1FINALGroupedMetricCentreComparison], on the top button, it will select all metrics and produce the full report based on the 3 centres i have in this test database.

The second button is linked to the combo box. This requires a selection to be made and the report displays based on the selection.

These all run perfectly.

In this database the date range is from SP10 (2012) to SP9 (2013). These are what we call Settlement Periods. There are 13 SPs are included in these reports. Please see the tblSPList for more details.

SO next SP is SP10(2013). So i untick SP10(2012) and tick SP13(2013) to represent the SPs that we need. I do have id numbers in this table.. i thought they might come in handy for this exact reason of making the report dynamic.

The crosstab query works pefectly when each SP changes due to the "true" field. So i dont need to change anything in the queries.

However the text fields in the reports are hard coded to the SP headings in the crosstab. this is where i'm coming unstuck. i need to manually change the record source for each of the text boxes. ( aand currently the labels. i'll work on that later).

It wouldn't be a problem if i only had the one report. however all up i have about 10 differnet reports in my main database.

Ok. My next question is:

I have added a TEST report button on the frmMetricSelect as well, as this is another problem i have. I have linked this to TEST versions of the same tables and queries as the above. but i have deleted data for the metric Adherence in SP11(2012). I did this to help find a solution. i have a problem when running a report when there is no data for a particular SP. Generally this is not a problem for the reports that cover all centres and all metrics as i can guarantee there is data for all.

However when I filter my report down if there is no data for a SP i receive a runtime error. 3070 - The microsoft database does not recognise '[SP11 (2012)' as a valid field name or expression.

I would like to know how I can get around this error. and still produce the report with just blanks in the SP11(2012) column. (I use blanks in the cells with no text not zero. it is neater for the purpose of the reports).

I would appreciate any help on both of my problems.

I'll attache my database. please let me know if your having problems opening it. (i've never attached anything to a forum before)

Thanks

 

[JHB]

Now it should work like you want.

 

[jules68]

Thank you so muc JBH, your attached file looks great. I'll try it this week on my full database.

I'll let you know how i go.

 

[JHB]

I'll let you know how i go.

Yes do that and not only if it not works , (and read my signatur.)

 

[jules68]

Thank you JHB that has all worked perfectly. very much appreciated.

Although I don't understand all of the code, i do understand some of it, i'm learning.... Thank you.

I do have another question about the database.

Currently I update the tblSPList manually each month, I update the currentSP, Last3SPs and the Last13SPs. although this is only a few clicks to update, are you able to assist with some code to update this through a button each month, maybe based on the new table you have show me??

so when we have SP10, data, I manually tick the CurrentSP as SP10, then i move the Last 3SPs to be 8,9 & 10. and the same for the last13SPs.

Again your help is greatly appreciated.

 

[JHB]

I have made it such that you select from a drop-down list the highest SP No, then the table "tblSPList" will be filled in automatically.
Open form ChooseSP and select from the drop-down list, then run your report.

 

[jules68]

Thank you very much JBH. It works perfectly.
Thank you to everyone who offered assistance. i have learnt so much from everyones responses.

 

[jules68]

Hi, i'm after a little more help to understand something. In my questions i asked

However when I filter my report down if there is no data for a SP i receive a runtime error. 3070 - The microsoft database does not recognise '[SP11 (2012)' as a valid field name or expression.

I would like to know how I can get around this error. and still produce the report with just blanks in the SP11(2012) column. (I use blanks in the cells with no text not zero. it is neater for the purpose of the reports).

Within the rpt1FINALGroupedMetricCentreComparisonJHB i can see that it doesn't matter if there is data or not. The report is still running and not bugging out when there is no data for a particular SP.

My problem is that I'm not able to locate how the report is doing this ???

I have copied all of the new code into my live database and it is all running great, except for this part. Can you please let me know how the report is doing this?

Thanks

 

[JHB]

Hi, i'm after a little more help to understand something. In my questions i asked

However when I filter my report down if there is no data for a SP i receive a runtime error. 3070 - The microsoft database does not recognise '[SP11 (2012)' as a valid field name or expression.

I would like to know how I can get around this error. and still produce the report with just blanks in the SP11(2012) column. (I use blanks in the cells with no text not zero. it is neater for the purpose of the reports).

Within the rpt1FINALGroupedMetricCentreComparisonJHB i can see that it doesn't matter if there is data or not. The report is still running and not bugging out when there is no data for a particular SP.

My problem is that I'm not able to locate how the report is doing this ???

I have copied all of the new code into my live database and it is all running great, except for this part. Can you please let me know how the report is doing this?

Thanks

It works because I have created a (temporary) table which contains the number of fields I needed.
Then I create a LEFT JOIN from the temporary table to the query that retrieves the dataerne, that's all.
The report does nothing, it is in the query.

 

[jules68]

Thanks for explaining this. I thought there might have been something within the query or report. But this makes sense now.
Much appreciated.

 

[jules68]

Hi, the above code that JHB supplied has been working perfectly in access 2007 i have now upgraded to 2010 and the code is bugging out at the rst.edit

Please see code below.

Sub CreateFieldNameTable()

Dim dbs As Database, rst As Recordset

Set dbs = CurrentDb

dbs.Execute ("SELECT '' AS FieldName, DisplaySP, Last13SPs Into tblReportFieldNames " _
& "FROM tblSPList WHERE Last13SPs = True")
Set rst = dbs.OpenRecordset("tblReportFieldNames", dbOpenDynaset)
If Not rst.EOF Then
Do
rst.Edit
rst![FieldName] = "F" & Format(rst.AbsolutePosition + 1, "00")
rst.Update
rst.MoveNext
Loop Until rst.EOF

End If
rst.Close

End Sub

Can anyone help?

thanks
Jules

 

[JHB]

... and the code is bugging out at the rst.edit

What do you mean with the above, is the an error number and error message, the show it.
The problem could be with missing/wrong References.
I've MS-Access 2010 and it runs without problem.
Else post you database with some sample data + information which form/report/data to use, to get the error.

 

[alaaassem]

hello JBH,

i am trying to do something looks like this in the post, but in a different way, can you help me please doing so?

my db is about creating a survey containing some questions and its answers for a branch and its sales team, i need a crosstab report that shows the branch and avg of the score for each sales person for each question.

i didn't try the sample you have posted here, but would it work it i take it and modify to comply with my database?

Thank you very much in advance.

Regards,

 

[JHB]

Could you show some "raw" data in Excel or in a MS-Access database, and how you would like the result.

 

[arnelgp]

please check report "PBs Comparison_Crosstab".
it cannot be opened in design view.
please upload a working report.