Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstab Data (A2K)

    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.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab Data (A2K)

    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.

  3. #3
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Data (A2K)

    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 <pre>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
    </pre>

    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 " '* * "):
    <pre>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
    </pre>

    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.
    Attached Files Attached Files
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Crosstab Data (A2K)

    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.

  5. #5
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Data (A2K)

    Once again Hans, you are my saviour!!!! Thanks.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •