[AccessD] Determine Closest Two Records for a Given Value

Will this work for you?

SELECT MAX(Distance) AS FoundDistance
FROM tbl_YourTableName
Where Distance < 5
UNION 
SELECT MIN(Distance) AS FoundDistance
FROM tbl_YourTableName
Where Distance > 5
ORDER BY FoundDistance

John

--
John V. Ruff – The Eternal Optimist :-) 

“Commit to the Lord whatever you do, 
and your plans will succeed.” Proverbs 16:3

-------------- Original message -------------- 
From: Josh McFarlane <darsant at gmail.com> 

> OK, got a bit of a fun SQL problem. 
> 
> I have a table that contains a distance floating point value. Given a 
> certain record, I need to find the closest distance above and below 
> it in a seperate table (Same physical measurement system though, so I 
> can extrapolate the data. Is it possible to select the first record 
> above and below for a certain value? 
> 
> IE: 
> 
> I want data at distance 5. 
> 
> However, the table has: 
> 1 
> 3.4 
> 4.1 
> 6.3 
> 
> So, the SQL query should return records 4.1 and 6.3 so I can 
> extrapolate data from them. 
> 
> If this isn't possible, what would be the next best thing? Maybe two 
> queries that did something such as 
> TOP 1 Distance>5 and TOP 1 Distance<5? 
> 
> Thanks 
> -- 
> Josh McFarlane 
> 
> "Peace cannot be kept by force. It can only be achieved by understanding." 
> -Albert Einstein 
> -- 
> AccessD mailing list 
> AccessD at databaseadvisors.com 
> http://databaseadvisors.com/mailman/listinfo/accessd 
> Website: http://www.databaseadvisors.com