[AccessD] Determine Closest Two Records for a Given Value

So you want to select the MAX value less than  5 and the MIN value
greater than 5?  What do you want to happen if there is a 5 in the
table?  Does that get returned or not?  What happens if there is no
value less than or none greater than the distance?

Charlotte Foust


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Josh
McFarlane
Sent: Wednesday, December 07, 2005 9:02 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Determine Closest Two Records for a Given Value


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