linking consecutive records to previous record in a query

[Guest]

I have a table called drillholes with fields holeid and depth then other
variables.
it looks like this;

holeid depth azi
test1 0 210
test1 70 215
test2 20 80
test2 90 78
test2 150 81
stuff 0 0
stuuf2 20 18

I would like to bring in a second version of the table and join them by hole
and join the depth with the next depth record for the other tablegetting
something like this.

holeid depth azi depthnext
test1 0 210 70
test1 70 215
test2 20 80 90
test2 90 78 150
test2 150 81
stuff 0 0
stuuf2 20 18

anyone know how this could be done?

 

[Guest]

Rather than joining the table, I would use a correlated subquery or DMin() to
look up the next depth.

To use a correlated subquery, you would type the following expression as the
field source in the design grid:

(SELECT Min(depth) FROM drillholes AS VT WHERE VT.holeid = drillholes.holeid
AND VT.depth > drillholes.depth)

In the subquery above, VT is just an alias for a second instance of the
drillholes table. VT is often used as an alias in subqueries (stands for
virtual table), but any alias can be used.

The DMin syntax would be:

DMin("[depth]","drillholes","holeid = '" & [holeid] & "' AND depth > " &
[depth])

In either case, your results will be much faster if you create an index on
drillholes by holeid and depth (I would assume that this is likely already
the primary key, in which case you are fine). I would expect that the
subquery will be the faster option.

HTH, Ted Allen