Returning a number between two numbers

[cablegirl]

I have a problem which I can't seem to work out and I am sure there is any easy fix.

I have two tables that have information based on the depth down a drillhole. The tables are linked by HoleID.

One of the tables has surveys at intervals down the hole eg. 30m, 60m, 90m, the other table has discrete data for exact depths eg 33.5m or 57.2m

I want the query to show the discrete reading with the closest possible survey data from the survey table. eg. 33.5m gives the survey information for the 30m and 57.2m gives the survey information for 60m.

Thanking in advance for any assistance

 

[jzwp22]

You will need 2 nested queries within another query. One of the nested queries would find the upper survey depth, the other the lower survey depth. From there you can add a calculated field to find the difference between the two survey depths and your discrete data depth, and display the lower. I've attached a database; take a look at the query to see an example. I assumed that if the two differences were equal, that the upper depth would be returned.