Crosstab counts aggregated by day ranges

[Guest]

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

Field: ServiceDurationDays: [TermDate]-[InstallDate] Reason Reaso
Table: qryTerminDates qryTerminDate
Total: Group By Group By Coun
Crosstab: Row Heading Column Heading Valu
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
Jod

 

[John Viescas]

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)

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.

 

[Guest]

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-5
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-59
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 gettin
the data sorted correctly. You will have to add more values if servic
duration goes beyond 959 days - with leading spaces to compensate

--
John Viescas, autho
"Microsoft Office Access 2003 Inside Out
"Running Microsoft Access 2000
"SQL Queries for Mere Mortals
http://www.viescas.com
(Microsoft Access MVP since 1993

Hi all

field which holds 1 of 10 data values representing reasons for terminating
service). The value is a count of the occurence of Reason. The Row Headin
is ServiceDurationDays, the subtraction of TerminDate - InstallDate, whic
yields a row of numbers, each representing a number of days the service wa
utilized. Some of these numbers have corresponding count values in a Reaso
column or two, others have no Reason count values, because none was recorde
for a TerminDate. The design grid looks like this

Column Column2 Column
Field: ServiceDurationDays: [TermDate]-[InstallDate

Reason Reaso

Table

qryTerminDates qryTerminDate

Total: Group B

Group By Coun

Crosstab: Row Headin

Column Heading Valu

Sort
Criteria

values are showing fine, but the ServiceDurationDays are broken down to
finely. What I need is to have the ServiceDurationDays consolidated into 6
day 'buckets', aggregating the column reason count values accordingly

 

[John Viescas]

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)

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)

Hi all,

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:

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.

 

[Guest]

Hi John

I tried the (CLng([TermDate]-[InstallDate])\60) + 1 and got an error 'Invalid use of Null'. Incidentally, I only added the CLng without any additional brackets as they already existed around ([TermDate]-[InstallDate])

Here is the SQL behind my crosstab grid

TRANSFORM Count(AqryTermDates.Reason) AS CountOfReaso
SELECT 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") AS ServiceDurationDay
FROM (AqryInstallDates INNER JOIN tblProspSubscr ON AqryInstallDates.SubscriberID = tblProspSubscr.ProspSubscrID) INNER JOIN AqryTermDates ON tblProspSubscr.ProspSubscrID = AqryTermDates.SubscriberI
GROUP BY 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"
PIVOT AqryTermDates.Reason

As you can see, my crosstab query is based on two queries and a table (3 field lists).

Subquery 1 is AqryInstallDates

SELECT tblService.SubscriberID, tblService.ServiceDate AS InstallDat
FROM tblServic
WHERE (((tblService.ServcTypeID)=2))

Subquery 2 is AqryTermDates

SELECT tblService.SubscriberID, tblService.ServiceDate AS TermDate, tblCanclReasn.Reaso
FROM tblCanclReasn RIGHT JOIN tblService ON tblCanclReasn.CancelReasnID = tblService.TermReasnI
WHERE (((tblService.ServcTypeID)=4))

I used a right outer join so that the lack of a cancellation reason did not prohibit any of the dyna records

The 2 subqueries are joined to the table via SubscriberID (in both subqueries) to tblProspSubscr.ProspSubscrI

Hope that helps and again, your assistance is invaluable

Cheers
Jod

 

[John Viescas]

Well, it's complaining because some rows must have a Null in either TermDate
or InstallDate. Is this the case? You might be able to get around it with
something like:

(CLng(NZ([TermDate], CDate("31 DEC 2199")) - NZ([InstallDate], 0))\60) + 1

The Choose function should return a Null in these cases because the
calculated index will be larger than the number of entries in the list.

--
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)

Hi John,

I tried the (CLng([TermDate]-[InstallDate])\60) + 1 and got an error

'Invalid use of Null'. Incidentally, I only added the CLng without any
additional brackets as they already existed around
([TermDate]-[InstallDate]).

Here is the SQL behind my crosstab grid:

TRANSFORM Count(AqryTermDates.Reason) AS CountOfReason
SELECT 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") AS
ServiceDurationDays

FROM (AqryInstallDates INNER JOIN tblProspSubscr ON

AqryInstallDates.SubscriberID = tblProspSubscr.ProspSubscrID) INNER JOIN
AqryTermDates ON tblProspSubscr.ProspSubscrID = AqryTermDates.SubscriberID

GROUP BY 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")

PIVOT AqryTermDates.Reason;


As you can see, my crosstab query is based on two queries and a table (3 field lists).

Subquery 1 is AqryInstallDates:

SELECT tblService.SubscriberID, tblService.ServiceDate AS InstallDate
FROM tblService
WHERE (((tblService.ServcTypeID)=2));

Subquery 2 is AqryTermDates:

SELECT tblService.SubscriberID, tblService.ServiceDate AS TermDate, tblCanclReasn.Reason
FROM tblCanclReasn RIGHT JOIN tblService ON tblCanclReasn.CancelReasnID = tblService.TermReasnID
WHERE (((tblService.ServcTypeID)=4));

I used a right outer join so that the lack of a cancellation reason did

not prohibit any of the dyna records.

The 2 subqueries are joined to the table via SubscriberID (in both

subqueries) to tblProspSubscr.ProspSubscrID

 

[Guest]

Worked nicely, John. I want you to know how much I appreciate your query expertise: I purchased 'Microsoft Office Access 2003 Inside Out' today from Amazon.com! But a small contribution toward your continued learning and sharing efforts

Cheers
Jody

 

[John Viescas]

GREAT! (Both the solution and your purchase!)

--
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)

Worked nicely, John. I want you to know how much I appreciate your query

expertise: I purchased 'Microsoft Office Access 2003 Inside Out' today from
Amazon.com! But a small contribution toward your continued learning and
sharing efforts.