Write a query to retain records based on certain criteriaâ€

  • Thread starter Thread starter Jean
  • Start date Start date
This query should give you the first record(s) where there have been 6 or more
calls in the following 15 day period.

SELECT A.Account, A.TimeOfCall
FROM Table as A INNER JOIN Table as B
ON A.Account = B.Account and
A.TimeOfCall >= B.TimeOfCall and
A.TimeOfCall <= B.TImeOfCall + 15
GROUP BY A.Account, A.TimeOfCall
Having Count(*) >= 7

You might want to strip off the time component of the TimeOfCall fields if you
are going to count days - although this will slow the query down.

With the above results you could get all the records for the period

SELECT C.*
FROM Table as C
INNER JOIN TheQuery as Q
On C.Account = Q.Account
AND C.TimeOfCall >= Q.TimeOfCall
AND C.TimeOfCall <= Q.TimeOfCall + 15

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
 
Hey John -

thanks so much for the query. I have couple of questions, i just want to
make sure i understand it correctly:

The first query will give me the first record with the account# and the time
of the first call? Is that correct?

The second query will then retrun all records for accounts who called 7 or
more times within the 15 days period. What is Table C in this case? and The
Query would be the first query?

Thanks so much!!
 
Hi John I wrote the following query based on yours but it s not working, it
returns records that have called less than 7 times in some instances and the
15 days criteria is not 100% working.

Can you tell me if there is anything wrong with the following query:

SELECT [MS Incidents May-June 08].[Contract #], [MS Incidents May-June
08].[Product Level 2]
FROM [MS Incidents May-June 08] INNER JOIN [MS Incidents May-June 08] AS [MS
Incidents May-June 08_1]
ON [MS Incidents May-June 08].[Contract #] = [MS Incidents May-June
08_1].[Contract #] AND [MS Incidents May-June 08].[Product Level 2] =[MS
Incidents May-June 08_1].[Product Level 2] AND [MS Incidents May-June
08].[Transaction Date]>=[MS Incidents May-June 08_1].[Transaction Date]
And [MS Incidents May-June 08].[Transaction Date]<=[MS Incidents May-June
08_1].[Transaction Date]+15
GROUP BY [MS Incidents May-June 08].[Contract #], [MS Incidents May-June
08].[Product Level 2]
HAVING Count(*)>=7

I need to keep the Contract# and Product Level 2 of records that have:
1. The same Contract# and
2. The same Product Level 2 (call reason) and
3. the # of such records are higher or equal to 7 where the "transaction
date" is between a 15 day range.

The Transaction date has the following format: 6/15/2008 0:57

I am not sure why the query is not working!

Thanks!!
 
I've aliased your references to your table to A and B to make it easier
for me to follow.

SELECT A.[Contract #]
, A.[Product Level 2]
FROM [MS Incidents May-June 08] As A
INNER JOIN [MS Incidents May-June 08] AS B
ON A.[Contract #] = B.[Contract #]
AND A.[Product Level 2] = B.[Product Level 2]
AND A.[Transaction Date]>= B.[Transaction Date]
And A.[Transaction Date]<= B.[Transaction Date]+15
GROUP BY A.[Contract #], A.[Product Level 2]
HAVING Count(*)>=7

Ok, I THINK I see the problem. We need to include the Transaction Date
in the group by. If we don't then we are going to count the same date
multiple times in the range. Also, we may need to add only 14 days to
the Transaction date comparison. later.

Try The following and see if it works for you.

SELECT DISTINCT A.[Contract #]
, A.[Product Level 2]
FROM [MS Incidents May-June 08] As A
INNER JOIN [MS Incidents May-June 08] AS B
ON A.[Contract #] = B.[Contract #]
AND A.[Product Level 2] = B.[Product Level 2]
AND A.[Transaction Date]>= B.[Transaction Date]
And A.[Transaction Date]<= B.[Transaction Date]+14
GROUP BY A.[Contract #], A.[Product Level 2], A.[Transaction Date]
HAVING Count(*)>=7

One problem is that your Transaction Date seems to include a time -
which is fine. However, for purposes of this query you might be better
off if you stripped off the time (use DateValue function to do so). You
could miss an incident if the first time was at 10:05 AM and the seventh
time was at 10:30 AM 14 days. This modification might work to take care
of the time problem, but it will slow things down.


SELECT DISTINCT A.[Contract #]
, A.[Product Level 2]
FROM [MS Incidents May-June 08] As A
INNER JOIN [MS Incidents May-June 08] AS B
ON A.[Contract #] = B.[Contract #]
AND A.[Product Level 2] = B.[Product Level 2]
WHERE DateValue(A.[Transaction Date])>= DateValue(B.[Transaction Date])
And DateValue(A.[Transaction Date])<=
DateValue(DateAdd("d",14,B.[Transaction Date]))
GROUP BY A.[Contract #], A.[Product Level 2], A.[Transaction Date]
HAVING Count(*)>=7

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi John I wrote the following query based on yours but it s not working, it
returns records that have called less than 7 times in some instances and the
15 days criteria is not 100% working.

Can you tell me if there is anything wrong with the following query:

SELECT [MS Incidents May-June 08].[Contract #], [MS Incidents May-June
08].[Product Level 2]
FROM [MS Incidents May-June 08] INNER JOIN [MS Incidents May-June 08] AS [MS
Incidents May-June 08_1]
ON [MS Incidents May-June 08].[Contract #] = [MS Incidents May-June
08_1].[Contract #] AND [MS Incidents May-June 08].[Product Level 2] =[MS
Incidents May-June 08_1].[Product Level 2] AND [MS Incidents May-June
08].[Transaction Date]>=[MS Incidents May-June 08_1].[Transaction Date]
And [MS Incidents May-June 08].[Transaction Date]<=[MS Incidents May-June
08_1].[Transaction Date]+15
GROUP BY [MS Incidents May-June 08].[Contract #], [MS Incidents May-June
08].[Product Level 2]
HAVING Count(*)>=7

I need to keep the Contract# and Product Level 2 of records that have:
1. The same Contract# and
2. The same Product Level 2 (call reason) and
3. the # of such records are higher or equal to 7 where the "transaction
date" is between a 15 day range.

The Transaction date has the following format: 6/15/2008 0:57

I am not sure why the query is not working!

Thanks!!

John Spencer said:
This query should give you the first record(s) where there have been 6 or more
calls in the following 15 day period.

SELECT A.Account, A.TimeOfCall
FROM Table as A INNER JOIN Table as B
ON A.Account = B.Account and
A.TimeOfCall >= B.TimeOfCall and
A.TimeOfCall <= B.TImeOfCall + 15
GROUP BY A.Account, A.TimeOfCall
Having Count(*) >= 7

You might want to strip off the time component of the TimeOfCall fields if you
are going to count days - although this will slow the query down.

With the above results you could get all the records for the period

SELECT C.*
FROM Table as C
INNER JOIN TheQuery as Q
On C.Account = Q.Account
AND C.TimeOfCall >= Q.TimeOfCall
AND C.TimeOfCall <= Q.TimeOfCall + 15

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
Click to expand...
Click to expand...
 
Back
Top