Re: Reference to next record within a group

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" <SimonL@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1707FF74-84F3-43D2-94F3-7D0D689C2DB6@xxxxxxxxxxxxxxxx
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




.