If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
% of callers repeat/unique
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]); |
#2
|
|||
|
|||
% of callers repeat/unique
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" wrote in message ... 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]); |
Thread Tools | |
Display Modes | |
|
|