S
Simon L
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
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 said:Michel,
The Starting Sequenc query that you just suggested worked and returned with
1546 records. Some sample records here:
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 said: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 said: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
:
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