Re: Crosstab counts aggregated by day ranges

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: John Viescas (JohnV_at_nomail.please)
Date: 03/21/04 

Date: Sun, 21 Mar 2004 11:13:14 -0600

You don't show any criteria in your original grid - did you add some? What
does the SQL look like now?

You might need to add a convert to long:

(CLng([TermDate]-[InstallDate])\60) + 1

No, strings are just fine. 

-- 
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
"Jody" <anonymous@discussions.microsoft.com> wrote in message
news:5C23B001-026D-4EC4-A199-16A23CFF95EE@microsoft.com...
> Hi John,
>
> Thanks very much for your advice.  I put the expression in the field cell
of the first design grid column containing the 'Group By' Total and 'Row
Heading' Crosstab, and got 'Data type mismatch in criteria expression'
returned when I ran the query.
>
> Just for your knowledge, the data types underlying the TermDate and
InstallDate are both date/time.  Could there be an issue with returning text
strings to this crosstab field?
>
> Thanks again,
> Jody
>
>
>
>      ----- John Viescas wrote: -----
>
>      Here's one way:
>
>      ServiceDurationDays: Choose((([TermDate]-[InstallDate])\60) + 1, "
0-59
>      days", " 60-119 days", "120-179 days", "180-239 days", "240-299
days",
>      "300-359 days", "360-419 days", "420-479 days", "480-539 days",
"540-599
>      days", "600-659 days", "660-719 days", "720-779 days", "780-839
days",
>      "840-899 days", "900-959 days")
>
>      Note that the leading spaces in the first two values are critical to
getting
>      the data sorted correctly.  You will have to add more values if
service
>      duration goes beyond 959 days - with leading spaces to compensate.
>
>      -- 
>      John Viescas, author
>      "Microsoft Office Access 2003 Inside Out"
>      "Running Microsoft Access 2000"
>      "SQL Queries for Mere Mortals"
>      http://www.viescas.com/
>      (Microsoft Access MVP since 1993)
>      "Jody" <anonymous@discussions.microsoft.com> wrote in message
>      news:0D12906E-04DB-492C-B6AA-ECA004327FEC@microsoft.com...
>      > Hi all,
>      >> I'm creating a crosstab which uses a column heading field of
Reason (a
>      field which holds 1 of 10 data values representing reasons for
terminating a
>      service).  The value is a count of the occurence of Reason.  The Row
Heading
>      is ServiceDurationDays, the subtraction of TerminDate - InstallDate,
which
>      yields a row of numbers, each representing a number of days the
service was
>      utilized.  Some of these numbers have corresponding count values in a
Reason
>      column or two, others have no Reason count values, because none was
recorded
>      for a TerminDate.  The design grid looks like this:
>      >> Column 1
>      Column2                          Column 3
>      >> Field: ServiceDurationDays: [TermDate]-[InstallDate]
>      Reason                           Reason
>      > Table:
>      qryTerminDates               qryTerminDates
>      > Total: Group By
>      Group By                         Count
>      > Crosstab: Row Heading
>      Column Heading              Value
>      > Sort:
>      > Criteria:
>      >> Currently I am getting 983 dynaset records.  The column reasons
and count
>      values are showing fine, but the ServiceDurationDays are broken down
too
>      finely.  What I need is to have the ServiceDurationDays consolidated
into 60
>      day 'buckets', aggregating the column reason count values
accordingly.
>      >> Any help is greatly appreciated.
>      >>> Cheers,
>      > Jody
>      >>>>>

Relevant Pages

  • RE: Backups have Shadow Copy Problems
    ... Active backup destination: File ... Reason: The process cannot access the file because it is being used by ... Warning: Unable to open "C:\Documents and Settings\Administrator\Local ... Warning: Unable to open "C:\Program Files\Microsoft SQL ...
    (microsoft.public.windows.server.sbs)
  • RE: Backups have Shadow Copy Problems
    ... recovery model of the SQL Server database to Simple. ... Warning: Unable to open "C:\Program Files\Microsoft SQL ... Reason: The process cannot access the file because it is being used by ... Backup completed on 2/14/2006 at 3:39 PM. ...
    (microsoft.public.windows.server.sbs)
  • [Full-Disclosure] RE: SQL Slammer doing the rounds again?
    ... I was assuming that this was the only reason the poor netadmin ... the web designers and their choices; I can't speak to the issues ... Security Business Unit ... SQL Slammer doing the rounds again? ...
    (Full-Disclosure)
  • Re: Parameters Question
    ... which is going against a Microsoft Access 2003 Northwinds database. ... Suppose user2 wanted to only query for a contact name of 'Aria Cruz' ... > his SQL string. ... >> Access 2003 database than it would be for SQL Server using OleDB. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Backup erros
    ... You may have a SQL ... Error 800423f4 appears in the backup log file when you back up a volume by ... > Reason: The process cannot access the file because it is being used by> another process. ...
    (microsoft.public.windows.server.sbs)