Finding Closest Match To A Number In Another Table

[The Brown Growler]

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

The actual table with the Band values to be looked up has approximately 1500 records from various localities and different types of Band values such as A bands, S bands, D bands etc. 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

 

[WayneRyan]

TBG,

Probably not the most efficient way, but it will work.

Add a new column to your query:

TheBand: GetBand([CT])

Put this in a Public module:

Public Function GetBand(dblCT As Double) As String
Dim rst As DAO.Recordset
Dim sql As String

sql = "Select Top 1 *" & _
      "From   Bands " & _
      "Order By ABS(" & dblCT & " - GradeNum)"

Set rst = CurrentDb.OpenRecordset(sql)

GetBand = rst!Band

End Function

Wayne

 

[Pat Hartman]

The Band table needs ranges rather than a single point then you can use Between to select the correct row. If you can't change the table you have a much more complicated problem. You'll need to find the max value less than the grade value and compute the difference between the two. You'll then need to find the min value greater than the grade value and compute the difference between the two. You'll then need to compare the two computations to determine which record you want.

 

[The Brown Growler]

Wayne,
Thanks for the reply.
I set up the module and query but I am getting the undefined function error message when running the query.

I may have set up the module incorrectly as my skills with VB are very poor. My procedure was to create a new module and paste the code into it then save it as GetBands.

I have attached a word document which contains some images of the query, a very small amount of data and the module I created. If you have a moment to look at it perhaps you could see where I am going wrong. I am probably missing something quite simple !!


Pat,
Are you saying that the method provided by Wayne will not work ? Apologies for having to ask but I am not very skilled in this area so would not really know if a suggested solution was valid.

Regards
tbg

 

[Pat Hartman]

I didn't see Wayne's method when I posted. I think his might work and it certainly is simpler.

 

[WayneRyan]

tbg,

Your query and function look OK. I wouldn't name the Module "GetBand", but your function
is Public (good) and does reside in a Public module (good). Perhaps post your DB and we'll
see what's wrong.

I think that Pat is saying traditionally, a table like your Bands table has ranges. This
reduces the problem to:

Select B.Band
From   Q_Bands_Source As A Left Join Bands As B On
       A.CT Between B.GradeNumLo And B.GradeNumHigh

That can be remarkably simple.

But, since you are interested in only the shortest "difference" there's nothing to readily
compare the difference to.

Post your DB if you still have trouble recognizing the function.

Wayne

 

[The Brown Growler]

Wayne,

I edited the name of the module from GetBand to m_Fct_GB and the query actually runs and returns values so that has cleared up the Undefined Function issue.

However, although the query is running, it is allocating the wrong [TheBand] values to the [CT] values. A quick check on what should be allocated is possible by looking at the values stored in the [Bands] table.

The data is for a series of greyhound tracks and each individual track will have different assignations of the Bands to the numeric values. I think that the function may be working on some form of averaging over the greyhound track domain rather than allocating on the basis of the individual tracks and race distances as provided by the [TrkDst] ID Key.

On reflection, I do not think that I explained the requirement in full, particularly with reference to there being multiple distinct assignations of the bands to numbers depending on the track.

I have attached the database in a zip file in the hope that you may be able to clear up the issue.

Many thanks & Best Regards
tbg

 

[WayneRyan]

tbg,

Don't join the tables in the query.

Just use table T_CT.

The new column --> TheBand: GetBand([CT],[T_ct].[TrkDstKey])

The module:

Public Function GetBand(dblCT As Double, TrkDstKey As String) As String
Dim rst As DAO.Recordset
Dim sql As String

sql = "Select Top 1 * " & _
      "From   Bands " & _
      "Where  TrkD[B][SIZE="5"]i[/SIZE][/B]stKey = '" & TrkDstKey & "' " & _
      "Order By ABS(" & dblCT & " - GradeNum)"

Set rst = CurrentDb.OpenRecordset(sql)

GetBand = rst!Band

End Function

Wayne

 

[The Brown Growler]

Wayne,

It works a treat.

I have just realised that I caused a bit more confusion earlier by having the ID Key names as [TrkDstKey] in one table and [TrkDistKey] in another table. However, your capitalising the i in the code highlighted the disparity.

Many many thanks for sorting this out for me, I would never have been able to resolve it.

Best Regards
tbg

 

[WayneRyan]

tbg,

Gload to help.

Wayne