query to obtain a value from the next record down 1

[jrflanne]

Hi,
I have a question that might be simple for you to answer but I can't seem to figure it out. I've searched this forum some but can't seem to find the answer. The table structure is as follows:

Name Layer Depth
A1 alpha 50
A1 beta 100
A1 gamma 200
A1 zeta 350
A2 alpha 100
A2 beta 200
A2 zeta 400

What I would like to do is query on Layer Beta and return the next layer below it (query i and return i+1). So the resulting table would be:

Name Layer Depth
A1 gamma 200
A2 zeta 400

I can visualize how to do this via programming (like fortran, which makes me an old fart), but I don't know VB at all. I am a 2 month Access user, which makes me kinda dangerous. I appreciate any ideas you might have. Thanks.

 

[DotNetGnat]

SELECT NAME, LAYER, DEPTH FROM mytable t1 WHERE t1.DEPTH IN (SELECT TOP 1 DEPTH FROM mytable t2 WHERE t1.NAME=t2.NAME ORDER BY DEPTH DESC)

i have assumed here that you need the one with the maximum depth...

-DNG

 

[PHV]

What is the REAL sort order ?
i.e. how you do you know which row is the next layer for ANY given name ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jrflanne]

Thanks for responding. The sort order is by Name and then Depth. In geology-speak, the Layer is assumed to become older by increasing depth so Layers gamma and zeta are older than Layer beta. What I am interested in is what Layer does beta rest on.

 

[PHV]

Perhaps this (typed, untested) ?
SELECT A.Name, A.Layer, A.Depth
FROM yourTable A
WHERE A.Depth In (SELECT Min(Depth) FROM yourTable B WHERE B.Name=A.Name AND B.Depth>(SELECT Depth FROM yourTable C WHERE C.Name=B.Name AND C.Layer='beta'))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886