Retrieve next-to-last value?

  • Thread starter Thread starter Carl Rapson
  • Start date Start date
C

Carl Rapson

I have four fields that define my primary key - the first 3 are text fields,
and the 4th is a number which isn't necessarily sequential, but does
increase by some amount in each succeeding record. I need to retrieve the
record where the 4th field is equal to the next-to-highest value of all
records matching the first 3 fields. I've tried the following:

SELECT [Field 1],[Field 2],[Field 3],Max([Field 4]) AS [Previous Value]
FROM [My Table] AS A
GROUP BY [Field 1],[Field 2],[Field 3]
HAVING Max([Field 4]) <
(SELECT Max([Field 4]) FROM [My Table] AS B WHERE B.[Field 1]=A.[Field 1]
AND B.[Field 2]=A.[Field 2] AND B.[Field 3]=A.[Field 3]);

But that doesn't seem to work - it returns no records, when I know for
certain that there are records.

Does anyone have any suggestions as to how to approach this query?

Thanks for any assistance,

Carl Rapson
 
Try using two queries. The first one pulls the highest value. The second
query includes the first query and is joined on the first three text fields.
Set criteria for the combination as less than ( < ) the higest combination
from the first query.

Combination: [Text1] &[Text2] &[Text3] &[NumberField]
 
Try (***untested***):

SELECT A.[Field 1], A.[Field 2], A.[Field 3],
Max([Field 4]) AS [Previous Value]
FROM [My Table] AS A
WHERE ( A.[Field 4] <
(
SELECT Max(B.[Field 4])
FROM [My Table] AS B
WHERE (B.[Field 1]=A.[Field 1])
AND (B.[Field 2]=A.[Field 2])
AND (B.[Field 3]=A.[Field 3])
) )
GROUP BY A[Field 1], A.[Field 2], A.[Field 3]
 
Thanks Karl, combined with Van's suggestion below it got me on the right
track.

Carl

KARL DEWEY said:
Try using two queries. The first one pulls the highest value. The
second
query includes the first query and is joined on the first three text
fields.
Set criteria for the combination as less than ( < ) the higest
combination
from the first query.

Combination: [Text1] &[Text2] &[Text3] &[NumberField]

Carl Rapson said:
I have four fields that define my primary key - the first 3 are text
fields,
and the 4th is a number which isn't necessarily sequential, but does
increase by some amount in each succeeding record. I need to retrieve the
record where the 4th field is equal to the next-to-highest value of all
records matching the first 3 fields. I've tried the following:

SELECT [Field 1],[Field 2],[Field 3],Max([Field 4]) AS [Previous Value]
FROM [My Table] AS A
GROUP BY [Field 1],[Field 2],[Field 3]
HAVING Max([Field 4]) <
(SELECT Max([Field 4]) FROM [My Table] AS B WHERE B.[Field 1]=A.[Field 1]
AND B.[Field 2]=A.[Field 2] AND B.[Field 3]=A.[Field 3]);

But that doesn't seem to work - it returns no records, when I know for
certain that there are records.

Does anyone have any suggestions as to how to approach this query?

Thanks for any assistance,

Carl Rapson
Click to expand...
Click to expand...
 
Thanks Van, combining this with Karl's suggestion above I was able to figure
it out.

Carl

Van T. Dinh said:
Try (***untested***):

SELECT A.[Field 1], A.[Field 2], A.[Field 3],
Max([Field 4]) AS [Previous Value]
FROM [My Table] AS A
WHERE ( A.[Field 4] <
(
SELECT Max(B.[Field 4])
FROM [My Table] AS B
WHERE (B.[Field 1]=A.[Field 1])
AND (B.[Field 2]=A.[Field 2])
AND (B.[Field 3]=A.[Field 3])
) )
GROUP BY A[Field 1], A.[Field 2], A.[Field 3]


--
HTH
Van T. Dinh
MVP (Access)



Carl Rapson said:
I have four fields that define my primary key - the first 3 are text
fields, and the 4th is a number which isn't necessarily sequential, but
does increase by some amount in each succeeding record. I need to retrieve
the record where the 4th field is equal to the next-to-highest value of
all records matching the first 3 fields. I've tried the following:

SELECT [Field 1],[Field 2],[Field 3],Max([Field 4]) AS [Previous Value]
FROM [My Table] AS A
GROUP BY [Field 1],[Field 2],[Field 3]
HAVING Max([Field 4]) <
(SELECT Max([Field 4]) FROM [My Table] AS B WHERE B.[Field 1]=A.[Field 1]
AND B.[Field 2]=A.[Field 2] AND B.[Field 3]=A.[Field 3]);

But that doesn't seem to work - it returns no records, when I know for
certain that there are records.

Does anyone have any suggestions as to how to approach this query?

Thanks for any assistance,

Carl Rapson
Click to expand...
Click to expand...
 
Back
Top