How to determine the fourth out of n records

I need to import a call log and determine the datetime of each
person's fourth out of potentially many more calls (ordered by date).
Obviously this is only for those with four or more calls.

1/1/2007 5pm
1/2/2007 6pm
1/3/2007 4am
1/3/2007 5pm * I want this one
1/4/2007 4pm
1/4/2007 6pm
....

I can do this in vba, but does anyone any other clever ways to do this
in just SQL?

Re: How to determine the fourth out of n records

from the query window I get [ ] brackets followed by a period .

SELECT Max(datefld) AS Expr1 FROM [select top 4 datefld from tblx Where
caller = 'joe123' order by datefld]. AS t1

If I were to run this from DoCmd.RunSql I would write it this way

DoCmd.RunSql "SELECT Max(datefld) As Expr1 FROM (select top 4 datefld
from tblx Where caller = 'joe123'
order by datefld) AS t1"

I would write it this way because that is how I would write it in tSql
(for sql server)

Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: How to determine the fourth out of n records

"Phil Stanton" wrote in
news:13pkrpsq525f5e9@corp.supernews.com:

> Guess you could have 1 Query1 that selected the Top 4
>
> SELECT TOP 4 LogDates.*
> FROM Log
> ORDER BY LogDates;
>
> Then a second Query based on the First
>
> SELECT DLast("LogDates","Query1") AS TheDate
> FROM Query1
> GROUP BY DLast("LogDates","Query1");
>
> Phil

One could expand on this principle with

SELECT TOP 1 P.* FROM Logdates ALIAS P
WHERE P.Logdate NOT IN (
SELECT TOP 3 Logdate FROM Logdates ALIAS C
WHERE Criteria ORDER BY C.Logdate)
ORDER BY P.Logdate;

Q



>
> wrote in message
> news:d7736508-66d0-4107-82d4-
6ffa6fc3580c@m34g2000hsb.googlegroups.
> com...
>>I need to import a call log and determine the datetime of each
>> person's fourth out of potentially many more calls (ordered by
>> date). Obviously this is only for those with four or more calls.
>>
>> 1/1/2007 5pm
>> 1/2/2007 6pm
>> 1/3/2007 4am
>> 1/3/2007 5pm * I want this one
>> 1/4/2007 4pm
>> 1/4/2007 6pm
>> ...
>>
>> I can do this in vba, but does anyone any other clever ways to do
>> this in just SQL?
>>
>>
>>
>>
>
>
>



--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Re: How to determine the fourth out of n records

Guess you could have 1 Query1 that selected the Top 4

SELECT TOP 4 LogDates.*
FROM Log
ORDER BY LogDates;

Then a second Query based on the First

SELECT DLast("LogDates","Query1") AS TheDate
FROM Query1
GROUP BY DLast("LogDates","Query1");

Phil

wrote in message
news:d7736508-66d0-4107-82d4-6ffa6fc3580c@m34g2000hsb.google groups.com...
>I need to import a call log and determine the datetime of each
> person's fourth out of potentially many more calls (ordered by date).
> Obviously this is only for those with four or more calls.
>
> 1/1/2007 5pm
> 1/2/2007 6pm
> 1/3/2007 4am
> 1/3/2007 5pm * I want this one
> 1/4/2007 4pm
> 1/4/2007 6pm
> ...
>
> I can do this in vba, but does anyone any other clever ways to do this
> in just SQL?
>
>
>
>

Re: How to determine the fourth out of n records

On Jan 25, 6:13 pm, Bob Quintal wrote:
> "Phil Stanton" wrote innews:13pkrpsq525f5e9@corp.supernews.com:
>
> > Guess you could have 1 Query1 that selected the Top 4
>
> > SELECT TOP 4 LogDates.*
> > FROM Log
> > ORDER BY LogDates;
>
> > Then a second Query based on the First
>
> > SELECT DLast("LogDates","Query1") AS TheDate
> > FROM Query1
> > GROUP BY DLast("LogDates","Query1");
>
> > Phil
>
> One could expand on this principle with
>
> SELECT TOP 1 P.* FROM Logdates ALIAS P
> WHERE P.Logdate NOT IN (
> SELECT TOP 3 Logdate FROM Logdates ALIAS C
> WHERE Criteria ORDER BY C.Logdate)
> ORDER BY P.Logdate;
>
> Q
>
>
>
> > wrote in message
> > news:d7736508-66d0-4107-82d4-
>
> 6ffa6fc35...@m34g2000hsb.googlegroups.
>
>
>
> > com...
> >>I need to import a call log and determine the datetime of each
> >> person's fourth out of potentially many more calls (ordered by
> >> date). Obviously this is only for those with four or more calls.
>
> >> 1/1/2007 5pm
> >> 1/2/2007 6pm
> >> 1/3/2007 4am
> >> 1/3/2007 5pm * I want this one
> >> 1/4/2007 4pm
> >> 1/4/2007 6pm
> >> ...
>
> >> I can do this in vba, but does anyone any other clever ways to do
> >> this in just SQL?
>
> --
> Bob Quintal
>
> PA is y I've altered my email address.
>
> --
> Posted via a free Usenet account fromhttp://www.teranews.com


I appreciate the replies, but sorry, my bad, I think I was a little
too vague. There are several thousands of people per month that need
the fourth phone call flagged. A top n query works great when dealing
with one person at a time, but this would be inefficient for my
purpose. This is why I stated I wasn't sure how to do this without
using VBA. I do believe that SQL can be used to do this, with a long
series of nested series:

qry1: select PersonID, min(calldate) from tbl groupby PersonID
qry2: select RecID from tbl where PersonID = (select PersonID from
qry1) and calldate = (select MinOfCallDate from qry1)
qry3: select tbl.* from tbl where RecID not in (select RecID from
qry2)
qry4: select select PersonID, min(calldate) from qry3 groupby
PersonID
....

This would be extremely slow when running, though it would be faster
using "outer join where null" queries instead of "not in" queries.
But still, I think I'm limited to either using VBA to accomplish the
task, or something like the pseudo-SQL I just wrote above. This is
not the first time I've encountered this general problem, and I've
always wondered if there's a SQL solution. What I wrote above would
work, though it's an inefficient brute-force method. Anyone have
anything else?

Re: How to determine the fourth out of n records

I have a table of Dividends from which I can select the 4th record for each
investment. Play with yhis and I think it will work

First query Query1

SELECT Dividends.InvestmentID, Dividends.DivDate, (Select Count(1) FROM
Dividends A WHERE A.DivDate <=Dividends.DivDate AND A.InvestmentID =
Dividends.InvestmentID) AS Sequence
FROM Dividends
ORDER BY Dividends.InvestmentID, Dividends.DivDate DESC;

That may translate as

SELECT LogCalls.PersonID, LogCalls.CallDate, (Select(Count(1) FROM LogCalls
A WHERE A.CallDate <= LogCalls.CallDate AND A.PersonID = LogCalls.PersonID)
AS Sequence
FROM LogCalls
ORDER BY LogCalls.PersonID, LogCalls.CallDate DESC;

Second Query

SELECT Query1.*
FROM Query1
WHERE (((Query1.Sequence)=4));

Don't ask me how it works

Phil


wrote in message
news:493eb8da-6ad0-48a5-ad4c-7de9ce793525@v67g2000hse.google groups.com...
> On Jan 25, 6:13 pm, Bob Quintal wrote:
>> "Phil Stanton" wrote
>> innews:13pkrpsq525f5e9@corp.supernews.com:
>>
>> > Guess you could have 1 Query1 that selected the Top 4
>>
>> > SELECT TOP 4 LogDates.*
>> > FROM Log
>> > ORDER BY LogDates;
>>
>> > Then a second Query based on the First
>>
>> > SELECT DLast("LogDates","Query1") AS TheDate
>> > FROM Query1
>> > GROUP BY DLast("LogDates","Query1");
>>
>> > Phil
>>
>> One could expand on this principle with
>>
>> SELECT TOP 1 P.* FROM Logdates ALIAS P
>> WHERE P.Logdate NOT IN (
>> SELECT TOP 3 Logdate FROM Logdates ALIAS C
>> WHERE Criteria ORDER BY C.Logdate)
>> ORDER BY P.Logdate;
>>
>> Q
>>
>>
>>
>> > wrote in message
>> > news:d7736508-66d0-4107-82d4-
>>
>> 6ffa6fc35...@m34g2000hsb.googlegroups.
>>
>>
>>
>> > com...
>> >>I need to import a call log and determine the datetime of each
>> >> person's fourth out of potentially many more calls (ordered by
>> >> date). Obviously this is only for those with four or more calls.
>>
>> >> 1/1/2007 5pm
>> >> 1/2/2007 6pm
>> >> 1/3/2007 4am
>> >> 1/3/2007 5pm * I want this one
>> >> 1/4/2007 4pm
>> >> 1/4/2007 6pm
>> >> ...
>>
>> >> I can do this in vba, but does anyone any other clever ways to do
>> >> this in just SQL?
>>
>> --
>> Bob Quintal
>>
>> PA is y I've altered my email address.
>>
>> --
>> Posted via a free Usenet account fromhttp://www.teranews.com
>
>
> I appreciate the replies, but sorry, my bad, I think I was a little
> too vague. There are several thousands of people per month that need
> the fourth phone call flagged. A top n query works great when dealing
> with one person at a time, but this would be inefficient for my
> purpose. This is why I stated I wasn't sure how to do this without
> using VBA. I do believe that SQL can be used to do this, with a long
> series of nested series:
>
> qry1: select PersonID, min(calldate) from tbl groupby PersonID
> qry2: select RecID from tbl where PersonID = (select PersonID from
> qry1) and calldate = (select MinOfCallDate from qry1)
> qry3: select tbl.* from tbl where RecID not in (select RecID from
> qry2)
> qry4: select select PersonID, min(calldate) from qry3 groupby
> PersonID
> ...
>
> This would be extremely slow when running, though it would be faster
> using "outer join where null" queries instead of "not in" queries.
> But still, I think I'm limited to either using VBA to accomplish the
> task, or something like the pseudo-SQL I just wrote above. This is
> not the first time I've encountered this general problem, and I've
> always wondered if there's a SQL solution. What I wrote above would
> work, though it's an inefficient brute-force method. Anyone have
> anything else?
>
>

Re: How to determine the fourth out of n records

That works, very clever! Thanks, Phil.

Re: How to determine the fourth out of n records

Please check it carefully. What happens if there are only 3 calls

Phil

wrote in message
news:2e0f65f9-725c-4dd6-9bcb-649780af8d93@s12g2000prg.google groups.com...
> That works, very clever! Thanks, Phil.