Matching Closest Values

[derekstewart]

I have 2 sets of prices and would like to match the two closest of the two sets. Here's an example:

Item#/LowPrice/MedPrice/HighPrice/Our1stPrice/Our2ndPrice/Our3rdprice.

445543/$2/$4/$6/$3.50/$8.00/$1.25

I want to find the value from the last 3 that most closely matches a value from the first 3. In this case the 3.50 most closely matches $4. I would like to return both the $3.50 and the $4.
445543/$4/$3.50

Any suggestions? Thanks.

 

[dhookom]

I would create a function (could probably be more efficient) and query:

Function GetMatch(pcurL As Currency, pcurM As Currency, _
    pcurH As Currency, pcur1 As Currency, pcur2 As Currency, _
    pcur3 As Currency, pint12 As Integer) As Currency
    'pint12 value of 1 is for returning the L, M, or H value
    'pint12 value of 2 is for returning the 1st, 2nd, or 3rd value
    Dim cur1 As Currency 'for best match of LMH
    Dim cur2 As Currency 'for best match of 1,2,3
    Dim curDif As Currency 'for the difference
    curDif = Abs(pcurL - pcur1)
    cur1 = pcurL
    cur2 = pcur1
    If Abs(pcurL - pcur2) < curDif Then
        curDif = Abs(pcurL - pcur2)
        cur1 = pcurL
        cur2 = pcur2
    End If
    If Abs(pcurL - pcur3) < curDif Then
        curDif = Abs(pcurL - pcur3)
        cur1 = pcurL
        cur2 = pcur3
    End If
    If Abs(pcurM - pcur1) < curDif Then
        curDif = Abs(pcurM - pcur1)
        cur1 = pcurM
        cur2 = pcur1
    End If
    If Abs(pcurM - pcur2) < curDif Then
        curDif = Abs(pcurM - pcur2)
        cur1 = pcurM
        cur2 = pcur2
    End If
    If Abs(pcurM - pcur3) < curDif Then
        curDif = Abs(pcurM - pcur3)
        cur1 = pcurM
        cur2 = pcur3
    End If
    If Abs(pcurL - pcur1) < curDif Then
        curDif = Abs(pcurL - pcur1)
        cur1 = pcurL
        cur2 = pcur1
    End If
    If Abs(pcurL - pcur2) < curDif Then
        curDif = Abs(pcurL - pcur2)
        cur1 = pcurL
        cur2 = pcur2
    End If
    If Abs(pcurL - pcur3) < curDif Then
        curDif = Abs(pcurL - pcur3)
        cur1 = pcurL
        cur2 = pcur3
    End If
    If pint12 = 1 Then
        GetMatch = cur1
     Else
        GetMatch = cur2
    End If
End Function

===Query SQL===========
SELECT tblPrices.*,
GetMatch([LowPrice],[MedPrice],[HighPrice],[Our1stPrice],[Our2ndPrice],[Our3rdPrice],1) AS LMH,
GetMatch([LowPrice],[MedPrice],[HighPrice],[Our1stPrice],[Our2ndPrice],[Our3rdPrice],2) AS Our
FROM tblPrices;

Duane
MS Access MVP
Find out how to get great answers faq219-2884.