Results 1 to 5 of 5
  1. #1
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Monitoring correspondence (2000)

    I have a table to record correspondence received, with fields called DateReceived and DateReplyDue (14 days after date of receipt) and DateReplySent. I need to generate reports that count how many were replied to within the due period and how many outside this period, how many have not yet been replied to - again within and without the due period. Finally I need the report to display the percentage replied to within the due time.
    Anyway, the question is.. would this be obtained most easily from a Report based on a Crosstab Query.. or do I need to base the report on two queries which produce counts and then sums? I hope that you get the gist..? Thanks, Andy.

  2. #2
    Star Lounger
    Join Date
    Dec 2001
    Location
    Houston
    Posts
    71
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monitoring correspondence (2000)

    I would do the latter, or more to the point, avoid doing a crosstab at all costs. The key to the latter approach is creating an identifier for the time periods of interest, and grouping on it. It could be a counter, or a date (such as period start date or period end date), as long as it correctly denotes those groups. Once you have that, you're home free.

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

    Re: Monitoring correspondence (2000)

    You don't really need a DateReplyDue field, since it is derived from DateReceived. You can create a query based on your table that returns flags (true/false) for the various statuses:

    SELECT tblDates.ID, tblDates.DateReceived, tblDates.DateReplySent, [DateReceived]+14 AS DateReplyDue, IIf([DateReplySent] Is Null,0,[DateReplySent]<=[DateReplyDue]) AS RepliedOK, IIf([DateReplySent] Is Null,0,[DateReplySent]>[DateReplyDue]) AS RepliedLate, ([DateReplySent] Is Null) And ([DateReplyDue]>=Date()) AS NotRepliedOK, ([DateReplySent] Is Null) And ([DateReplyDue]<Date()) AS NotRepliedLate
    FROM tblDates;

    Where tblDates is the name of the table. Let's say you save this as qryStatus. Create another query based on the first one that computes counts and percentages:

    SELECT Abs(Sum([RepliedOK])) AS CountRepliedOK, Abs(Sum([RepliedLate])) AS CountRepliedLate, Abs(Sum([NotRepliedOK])) AS CountNotRepliedOK, Abs(Sum([NotRepliedLate])) AS CountNotRepliedLate, Count(*) AS CountTotal, [CountRepliedOK]/[CountTotal] AS PercRepliedOK, [CountRepliedLate]/[CountTotal] AS PercRepliedLate, [CountNotRepliedOK]/[CountTotal] AS PercNotRepliedOK, [CountNotRepliedLate]/[CountTotal] AS PercNotRepliedLate
    FROM qryStatus;

    See attached demo.
    Attached Files Attached Files

  4. #4
    5 Star Lounger
    Join Date
    Mar 2001
    Posts
    989
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Monitoring correspondence (2000)

    Sorry to be a pain but can this attachment be re-posted in Access 97 format? And thank you, I believe this is exactly the information I need. Andy.

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

    Re: Monitoring correspondence (2000)

    Here you go.
    Attached Files Attached Files

Posting Permissions

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