Reference to next record within a group

Michel,
As I said in my last post the new starting Sequence query using the tempory
tables returned 1546 records for my small data sample. However, when I ran it
thru the original starting sequence it returned only 721 records. Something
is not right. I believe the original starting sequence query is correct as I
manually verified the final result.

For your information, my small data sample has 1791 records. There are 210
records considered as duplicate as a mbr can have two or more different
medications filled on the same day for the same days supply.

Simon

"Simon L" wrote:

Michel,
The Starting Sequenc query that you just suggested worked and returned with
1546 records. Some sample records he

memberID NextFillDate
940070653-01 10/21/2006
940070653-01 11/30/2006
940070653-01 12/16/2006
940070653-01 1/1/2007
940070653-01 1/17/2007
940076306-01 3/4/2006
940076306-01 4/8/2006
940076306-01 5/17/2006
940076306-01 6/24/2006
940076306-01 8/1/2006
940076306-01 9/12/2006
940076306-01 10/18/2006
940076306-01 11/21/2006
940076306-01 12/25/2006

I tried the Ending sequence with the following query but I got the same
error message of " At most one record can be returned by this query."

SELECT DISTINCT a.memberID, a.NextFillDate
FROM [Test 1] AS a
WHERE 0=(SELECT COUNT(*)
FROM [Test 1] AS b
WHERE a.memberID=b.memberID AND
b.FillDate = a.NextFillDate)
- (SELECT sq1Count
FROM tempCountNextFill AS c
WHERE a.memberID=c.memberID AND
c.NextFillDate= a.NextFillDate)

If I changed it to the following, I got zero record return.

SELECT DISTINCT a.memberID, a.NextFillDate
FROM [Test 1] AS a
WHERE 0=(SELECT COUNT(*)
FROM [Test 1] AS b
WHERE a.memberID=b.memberID AND
b.FillDate = a.NextFillDate)
- (SELECT MAX(sq1Count)
FROM tempCountNextFill AS c
WHERE a.memberID=c.memberID AND
c.NextFillDate= a.NextFillDate)

I feel like we are getting close. Please bear with me as I am trying to
learn this. I truly appreciate your help.

Simon


"Michel Walsh" wrote:

That is bad news, we should have only one record for a given memberID and a
given NextFillDate. Hoping for the best, but I suspect the result may not be
right, try:


SELECT DISTINCT a.memberID, a.NextFillDate
FROM [Test 1] AS a
WHERE 0=(SELECT MAX(sq2Count)
FROM tempCountFillDate AS b
WHERE a.memberID=b.memberID AND
b.NextFillDate =a.NextFillDate)
- (SELECT MAX(sq1Count)
FROM tempCountNextFill AS c
WHERE a.memberID=c.memberID AND
c.NextFillDate= a.NextFillDate)



Vanderghast, Access MVP


"Simon L" wrote in message
...
Michel,
Thanks again for the response.
I did test the two temp tables on a small set of data and both tables
returned the results. However when I tried to run the starting sequence
using
the following query, it gave me an error message, " At most one record can
be
returned by this query." What did I do wrong?

SELECT DISTINCT a.memberID, a.NextFillDate
FROM [Test 1] AS a
WHERE 0=(SELECT sq2Count
FROM tempCountFillDate AS b
WHERE a.memberID=b.memberID AND
b.NextFillDate =a.NextFillDate)
- (SELECT sq1Count
FROM tempCountNextFill AS c
WHERE a.memberID=c.memberID AND
c.NextFillDate= a.NextFillDate)
Simon


"Michel Walsh" wrote:

You make the temporary tables, first, on the small data set. Then, you
run
the new MedStartingSequence on the small data set to see if it gives the
same result (as it should).

Next, you repeat on the real data set, but instead of checking the
validity
of the result, you check the time it takes to run. Indeed, if it takes as
much time as previously, that is useless to continue in this direction.



Hoping it may help,
Vanderghast, Access MVP

If we restart from the working, but slow, solution (slow on a large set of data).



Create table tempCountNextFill with:

SELECT a.memberID, a.NextFillDate, COUNT(*) AS sq1Count
INTO tempCountNextFill

FROM meds AS a INNER JOIN meds AS c
ON a.memberID=c.memberID
AND a.NextFillDate = c.NextFillDate
GROUP BY a.memberID, a.NextFillDate



Create table tempCountFillDate with:

SELECT a.memberID, a.NextFillDate, COUNT(*) AS sq2Count
INTO tempCountFillDate
FROM meds AS a INNER JOIN meds AS b
ON a.memberID = b.memberID
AND b.fillDate = a.NextFillDate
GROUP BY a.memberID, a.NextFillDate

(you may add indexes on memberID and on NextFillDate fields, once the table are created, for each of these two temp tables).

Remplace

MedsEndingSequence memberID NextFillDate
940183763-01 2007.03.07
940183763-01 2007.08.02
940183763-01 2007.11.22
940183763-01 2007.12.24
940183763-01 2008.01.26


where its SQL statement was

SELECT DISTINCT a.memberID, a.NextFillDate
FROM meds AS a
WHERE 0=(SELECT COUNT(*) FROM meds AS b WHERE b.FillDate = a.NextFillDate)-(SELECT COUNT(*) FROM meds AS c WHERE c.NextFillDate= a.NextFillDate);

by:

SELECT DISTINCT a.memberID, a.NextFillDate
FROM meds AS a
WHERE 0=(SELECT sq2Count
FROM tempCountFillDate AS b
WHERE a.memberID=b.memberID AND
b.NextFillDate =a.NextFillDate)
- (SELECT sq1Count
FROM tempCountNextFill AS c
WHERE a.memberID=c.memberID AND
c.NextFillDate= a.NextFillDate)


which produces exactly the same result, on the small data set.


Check if this new query speed is acceptable. If not, we will have to forget that approach. On the other hand, if it becomes acceptable, we will have to change MedsStartingSequence in a similar way.




Hoping it may help,
Vanderghast, Access MVP

Michel,
Looks like we need to find another approach to handle a large number of
records. The new Ending Sequence query worked beautifully for a small sample
of 1791 records. The query completed in less than 10 sec. However, when I ran
it (after created two temp tables) for the large data set (108902 records),
it was still running after more than 5 hours and I had to terminate it.

Simon

"Michel Walsh" wrote:

If we restart from the working, but slow, solution (slow on a large set of data).



Create table tempCountNextFill with:

SELECT a.memberID, a.NextFillDate, COUNT(*) AS sq1Count
INTO tempCountNextFill

FROM meds AS a INNER JOIN meds AS c
ON a.memberID=c.memberID
AND a.NextFillDate = c.NextFillDate
GROUP BY a.memberID, a.NextFillDate



Create table tempCountFillDate with:

SELECT a.memberID, a.NextFillDate, COUNT(*) AS sq2Count
INTO tempCountFillDate
FROM meds AS a INNER JOIN meds AS b
ON a.memberID = b.memberID
AND b.fillDate = a.NextFillDate
GROUP BY a.memberID, a.NextFillDate

(you may add indexes on memberID and on NextFillDate fields, once the table are created, for each of these two temp tables).

Remplace

MedsEndingSequence memberID NextFillDate
940183763-01 2007.03.07
940183763-01 2007.08.02
940183763-01 2007.11.22
940183763-01 2007.12.24
940183763-01 2008.01.26


where its SQL statement was

SELECT DISTINCT a.memberID, a.NextFillDate
FROM meds AS a
WHERE 0=(SELECT COUNT(*) FROM meds AS b WHERE b.FillDate = a.NextFillDate)-(SELECT COUNT(*) FROM meds AS c WHERE c.NextFillDate= a.NextFillDate);

by:

SELECT DISTINCT a.memberID, a.NextFillDate
FROM meds AS a
WHERE 0=(SELECT sq2Count
FROM tempCountFillDate AS b
WHERE a.memberID=b.memberID AND
b.NextFillDate =a.NextFillDate)
- (SELECT sq1Count
FROM tempCountNextFill AS c
WHERE a.memberID=c.memberID AND
c.NextFillDate= a.NextFillDate)


which produces exactly the same result, on the small data set.


Check if this new query speed is acceptable. If not, we will have to forget that approach. On the other hand, if it becomes acceptable, we will have to change MedsStartingSequence in a similar way.




Hoping it may help,
Vanderghast, Access MVP

Can you give a last try with NewNewMedsEndingSequence:





SELECT DISTINCT a.memberID, a.NextFillDate
FROM (meds AS a INNER JOIN tempCountFillDate AS b
ON a.memberID=b.memberID AND
b.NextFillDate =a.NextFillDate)
INNER JOIN tempCountNextFill AS c
ON a.memberID=c.memberID AND
c.NextFillDate= a.NextFillDate AND
c.sq1Count = b.sq2.Count




( instead of the last try we did with NewMedsEndingSequence:

SELECT DISTINCT a.memberID, a.NextFillDate
FROM meds AS a
WHERE 0=(SELECT sq2Count
FROM tempCountFillDate AS b
WHERE a.memberID=b.memberID AND
b.NextFillDate =a.NextFillDate)
- (SELECT sq1Count
FROM tempCountNextFill AS c
WHERE a.memberID=c.memberID AND
c.NextFillDate= a.NextFillDate)
)



And add individual index on each field of the two temp tables, as well as on
memberID and NextFillDate of the original table Meds (if they are not
already there).




Hoping it may help,
Vanderghast, Access MVP



"Simon L" wrote in message
...
Michel,
Looks like we need to find another approach to handle a large number of
records. The new Ending Sequence query worked beautifully for a small
sample
of 1791 records. The query completed in less than 10 sec. However, when I
ran
it (after created two temp tables) for the large data set (108902
records),
it was still running after more than 5 hours and I had to terminate it.

Simon

Note that the 'new' solution just cover half the job. Compare only the
running time of NewNewMedsEndingSequence versus the running time of
MedsEndingSequence; NOT versus the whole problem. The running time of the
whole problem will be roughly twice the amount of time this query takes
(once we will optimize the second part too).


Vanderghast, Access MVP