% of callers repeat/unique

  • Thread starter Thread starter Al
  • Start date Start date
A

Al

I currently have a query that I use to review individuals who've called more
than once within a month (based on a few criteria, but most importantly a
field called Level-1 and Case-ID for counting # unique entries).

People are defined with the Name field in the SQL below (which is based on
IIF statement).

What I need to do is have a query that by month shows % repeat callers
against % non-repeat... This would be based on the following:

1) Repeat - Where Name field has more than one entry within the given month
and the conditions within the SQL (i.e. Name not like *[?]* and Closed-Date
is Not Null and Level-1 Not like z*)

2) Unique (Non-Repeat) - Where Name field contains only one case within the
given month

Ideally I'd like this to appear as follows:

Month Repeat % Non-Repeat %
Jan 5% 95%
Feb 7% 93%
Mar 4% 96%
etc...

Appreciate any help as I'm completely stumped on how to do this!!..

Thanks, Al.

SQL:
SELECT IIf([Customer-Type]="Internal",[First-Name-+] & " " &
[Last-Name-+],[Supplier-Name]) AS Name, [P2P-Request].[Organisation-Level-2],
[P2P-Request].[Level-1], Count([P2P-Request].[Case-ID]) AS [CountOfCase-ID],
Left(MonthName(Month([Create-Date])),3) AS [Month]
FROM [P2P-Request]
WHERE (((Year([Create-Date]))=[Enter Year]) AND
(([P2P-Request].[Closed-Date]) Is Not Null) AND (([P2P-Request].[Level-1])
Not Like "z*"))
GROUP BY IIf([Customer-Type]="Internal",[First-Name-+] & " " &
[Last-Name-+],[Supplier-Name]), [P2P-Request].[Organisation-Level-2],
[P2P-Request].[Level-1], Left(MonthName(Month([Create-Date])),3)
HAVING (((IIf([Customer-Type]="Internal",[First-Name-+] & " " &
[Last-Name-+],[Supplier-Name])) Not Like "*[?]*") AND
((Count([P2P-Request].[Case-ID]))>1))
ORDER BY IIf([Customer-Type]="Internal",[First-Name-+] & " " &
[Last-Name-+],[Supplier-Name]), Count([P2P-Request].[Case-ID]);
 
SELECT a.Name, MIN(b.Name) AS Translate
FROM myTable AS a INNER JOIN myTable AS b
ON a.Name LIKE "*" & b.Name & "*"
GROUP BY a.Name


produce a kind of translation table for duplicated name. Note that
accordingly to the rule it seems you want to use, "thin" matches "Within"
as well as "Thinking". Say that query is saved under the name q0.




SELECT a.YearMonth, b.Translate, COUNT(*) AS howManyTimes
FROM myTable AS a INNER JOIN q0
ON a.Name =b.Name
GROUP BY a.YearMonth, b.Translate


will give, for each month, each name, how many times it appears, for that
month..

Save that query, say under the name of q1. Then:


SELECT YearMonth,
SUM(iif(howManyTimes =1, 1, 0))/COUT(*) AS ratioUnique,
1 - ratioUnique AS ratioNotUnique
FROM q1
GROUP BY YearMonth


give the ratio of unique and not unique. Multiply it 100 (or format it) to
have it as a percentage.



Vanderghast, Access MVP



Al said:
I currently have a query that I use to review individuals who've called
more
than once within a month (based on a few criteria, but most importantly a
field called Level-1 and Case-ID for counting # unique entries).

People are defined with the Name field in the SQL below (which is based on
IIF statement).

What I need to do is have a query that by month shows % repeat callers
against % non-repeat... This would be based on the following:

1) Repeat - Where Name field has more than one entry within the given
month
and the conditions within the SQL (i.e. Name not like *[?]* and
Closed-Date
is Not Null and Level-1 Not like z*)

2) Unique (Non-Repeat) - Where Name field contains only one case within
the
given month

Ideally I'd like this to appear as follows:

Month Repeat % Non-Repeat %
Jan 5% 95%
Feb 7% 93%
Mar 4% 96%
etc...

Appreciate any help as I'm completely stumped on how to do this!!..

Thanks, Al.

SQL:
SELECT IIf([Customer-Type]="Internal",[First-Name-+] & " " &
[Last-Name-+],[Supplier-Name]) AS Name,
[P2P-Request].[Organisation-Level-2],
[P2P-Request].[Level-1], Count([P2P-Request].[Case-ID]) AS
[CountOfCase-ID],
Left(MonthName(Month([Create-Date])),3) AS [Month]
FROM [P2P-Request]
WHERE (((Year([Create-Date]))=[Enter Year]) AND
(([P2P-Request].[Closed-Date]) Is Not Null) AND (([P2P-Request].[Level-1])
Not Like "z*"))
GROUP BY IIf([Customer-Type]="Internal",[First-Name-+] & " " &
[Last-Name-+],[Supplier-Name]), [P2P-Request].[Organisation-Level-2],
[P2P-Request].[Level-1], Left(MonthName(Month([Create-Date])),3)
HAVING (((IIf([Customer-Type]="Internal",[First-Name-+] & " " &
[Last-Name-+],[Supplier-Name])) Not Like "*[?]*") AND
((Count([P2P-Request].[Case-ID]))>1))
ORDER BY IIf([Customer-Type]="Internal",[First-Name-+] & " " &
[Last-Name-+],[Supplier-Name]), Count([P2P-Request].[Case-ID]);
Click to expand...
 
Back
Top