• Crosstab Data (A2K)

    Author
    Topic
    #444985

    I have a report where the underlying data is a crosstab query and based on user input of time frame.

    The crosstab query: “Column Heading” is GOAL. A GOAL is selected by the user where the choices for GOALS are “MET”, “NOT MET”, and “PARTIALLY MET”.

    The problem is this:

    User selects a time frame, if the GOALS that apply for that time frame don’t have all 3 GOALS (Met, Not Met, Partially Met) then an error appears for the missing Goal(s).

    Currently I’ve tried to use Nz function but still it doesn’t recognize a GOAL that isn’t listed.

    What must I do to supress the issue when NOT ALL 3 GOALS are part of the data?

    Thanks for any suggestions.

    Viewing 0 reply threads
    Author
    Replies
    • #1077656

      Normally, a crosstab query returns a blank (null) if there are no data for a category, not an error, so we need to know more about the query.

      • #1077755

        Hans,

        I found this code from Microsoft:

        Very long piece of code (over 6,000 characters!) moved to attachment by HansV

        and all works as it should.

        I’ve modified the number of columns, originally 11 now 6. I need a column for the description of the GOAL, “MET”, “NOT MET” and “PARTIALLY MET”, “TOTAL” and “AVG”.

        Now if there is a category of GOALS missing, only 2 columns for the “GOALS” appear. For instance, if there aren’t any records for “NOT MET” in the time frame specified, then the description of the “GOAL”, “MET”, “PARTIALLY MET”, “TOTALS” and “AVG” for those two GOALS appear and every field has a value in it, except for “AVG”. I determined the “TOTAL” is coming from

        Private Sub GroupFooter0_Print(Cancel As Integer, PrintCount As Integer)
            
            Dim intX As Integer
            Dim lngRowTotal As Long
        
            '  If PrintCount is 1, initialize rowTotal variable.
            '  Add to column totals.
            If Me.PrintCount = 1 Then
                lngRowTotal = 0
                
                For intX = 2 To intColumnCount
                    '  Starting at column 2 (first text box with crosstab value),
                    '  compute total for current row in detail section.
                    lngRowTotal = lngRowTotal + Me("Col" + Format(intX))
                    '  Add crosstab value to total for current column.
                    lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" + Format(intX))
                Next intX
                
                '  Place row total in text box in detail section.
                Me("Col" + Format(intColumnCount + 1)) = lngRowTotal
                '  Add row total for current row to grand total.
                lngReportTotal = lngReportTotal + lngRowTotal
            End If
        End Sub
        

        and now I don’t know how to include the “AVG”.

        I can change the column count from 6 to 5 and put my own formula’s in the 6th column. The problem with this is, the value that would be needed to for the 6th column depends on col5, making col5 static, since the column count CAN be different it can’t be static.

        I’ve modified the previous Code to this (Noted by ” ‘* * “):

        Private Sub GroupFooter0_Print(Cancel As Integer, PrintCount As Integer)
            
            Dim intX As Integer
            Dim lngRowTotal As Long
            Dim lngRowAvg As Long
            Dim lngReportTotal As Long
        
            
            '  If PrintCount is 1, initialize rowTotal variable.
            '  Add to column totals.
            If Me.PrintCount = 1 Then
                lngRowTotal = 0
                
                For intX = 2 To intColumnCount
                    '  Starting at column 2 (first text box with crosstab value),
                    '  compute total for current row in detail section.
                    lngRowTotal = lngRowTotal + Me("Col" + Format(intX))
                 '   lngReportTotal = lngReportTotal + Me("Col" + Format(intX))
                 '   lngRowAvg = lngRowTotal / lngReportTotal
                 '   Debug.Print lngRowAvg
                    
                    '  Add crosstab value to total for current column.
                    lngRgColumnTotal(intX) = lngRgColumnTotal(intX) + Me("Col" + Format(intX))
                 '* *  lngRgColumnAvg(intX) = lngRgColumnAvg(intX) + Me("Col" + Format(intX))
                Next intX
                
                '  Place row total in text box in detail section.
                Me("Col" + Format(intColumnCount + 1)) = lngRowTotal
                '* *Me("Col" + Format(intColumnCount + 1)) = lngRowAvg
                
                '  Add row total for current row to grand total.
                lngReportTotal = lngReportTotal + lngRowTotal
                '* *lngReportAvg = lngReportAvg + lngRowAvg
            End If
        End Sub
        

        But several msg appear either, “no division by 0”, “Overflow”. How can I remedy this? If I comment out my additions, again the report provides the accurate values and doesn’t include the average, and this is what I need.

        I’ve also thought about using 3 crosstab queries, 1 to get the count per group, 2 to get the TOTAL SUM of the count, 3 to get the Average, but again the problem is I can’t include 3 values in a crosstab.

        • #1077766

          It is admirable that you tried to modify the code from Microsoft to suit your needs. But unfortunately, the method used to calculate row totals cannot easily be extended to row averages. When you calculate an average, you have to ignore null values: the average of { 4, null, 8 } is not 12 / 3 = 4 but 12 / 2 = 6: only the non-null values are included in the count.
          Also, while you can calculate the report total by adding the row totals together, you cannot simply add the row averages together.

          You can fix the number of columns in a crosstab query by specifying the Column Headings property of the query. If you set this to

          “MET”, “PARTIALLY MET”, “NOT MET”

          the query will always display these columns (in the specified order), whether there are data for each column or not. This means that you don’t need all this complicated code, but can design a report with fixed columns instead. That is much easier.

          You can create a query based on two or more crosstab queries, linked on the Goal field, and simulate a crosstab query with multiple value fields this way.

    Viewing 0 reply threads
    Reply To: Crosstab Data (A2K)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information: