Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2012

    Unanswered: Dynamic reporting from a crosstab query

    Hi all,

    I have a crosstab query for a complaints management database that summarizes the information on progress of a complaints by its severity.

    Status list is
    In Progress

    Severity List
    1 - Information only
    2 - Minor Issue
    3 - Major Issue
    4 - Serious Issue

    The query works fine on its own.
    The report takes the parameters (Date Range) from a Form.
    The reports works fine when there are all 4 severities recorded for the specified time period. The problem occurs when there are only two severities for the period.

    I have checked on the report, the controls of each field is mapped correctly.

    How can I get the query to either generate a 0 for the severity when none has been recorded for the specified period so that my report runs.
    A code for the Run Command button to populate 0 for the severity or status if they did not occur for the specified time period.
    i.e. If for 1/10/2012 - 31/10/2012 no Sev. 3 or 4 were recorded, put a 0 in the field.

    Help would be appreciated.

    Crosstab query SQL:
    PARAMETERS [Forms]![Summary Reports]![Text47] DateTime, [Forms]![Summary Reports]![Text49] DateTime;
    TRANSFORM Count(infoTBL.ID) AS CountOfID
    SELECT infoTBL.Status, Count(infoTBL.ID) AS [Total Of ID]
    FROM infoTBL
    WHERE (((infoTBL.[Date of Complaint]) Between [Forms]![Summary Reports]![Text47] And [Forms]![Summary Reports]![Text49]))
    GROUP BY infoTBL.Status, infoTBL.[Date of Complaint], infoTBL.[Date of Complaint]
    PIVOT infoTBL.Severity;

    Database attached
    Attached Files Attached Files

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 18
    Sorry, what problem occurs? Cross-tabs can only show you what's there, sadly. Any cells representing non-existent intersections will generally be Null.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    consider using the NZ function
    Microsoft Access tips: Crosstab query tips
    Display zeros (not blanks)

    Where there are no values, the column is blank. Use Nz() if you want to show zeros instead. Since Access frequently misunderstands expressions, you should also typecast the result. Use CCur() for Currency, CLng() for a Long (whole number), or CDbl() for a Double (fractional number.)

    Type the Nz() directly into the TRANSFORM clause. For the example above, use:
    TRANSFORM CLng(Nz(Sum([Order Details].Quantity),0)) AS SumOfQuantity
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2012
    Thanks for the reply. I will try this.

Tags for this Thread

Posting Permissions

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