Finding Closest Match To A Number Then Returning Field Value Linked To Number

[tbg]

Hi,

Could anyone please assist me with a query that I wish to set up. The
basis of which is to find the nearest value in one table to a specific
value from another table.

In a table named [Source] I have a field named [CT] which contains a
series of numerical values. In another joined table named [Bands] I
have a list of grades in a field named [Grade] and the numerical
values corresponding to those grades in a field named [GradeNum]

What I wish to do is take the [CT] numerical value and find the
closest possible match to it it the [GradeNum] field in the joined
table and return the [Grade] value.

The tables are joined by an ID key

Using the below list as an example of the [Bands] table, if my value
of [CT] was 29.93 from the [Source] table then the query result would
return A1. If the value of [CT] was 29.99 then the query result would
return A2

IDKey.... GradeNum..Band
Coventry-480m 29.90 A1
Coventry-480m 30.05 A2
Coventry-480m 30.09 A3
Coventry-480m 30.19 A4
Coventry-480m 30.27 A5
Coventry-480m 30.35 A6
Coventry-480m 30.44 A7
Coventry-480m 30.56 A8
Coventry-480m 30.67 A9
Coventry-480m 30.78 A10


I hope that a solution can be found whereby I can implement it in the
query grid as an expression as my SQL skills are limited and I have
quite a few other things going on in the same query.

Thx & Regards
tbg

 

[Ken Snell \(MVP\)]

Try this:

SELECT [Source].[CT],
(SELECT TOP 1 T.[Band]
FROM [Bands] AS T
ORDER BY Abs(T.[GradeNum] - [Source].[CT]),
T.[Band]) AS ClosestBand
FROM [Sources];