Access/SQL Select Query - Return "Most Like" Value Only

Question

We have a chargeback process in an AccessDB where Departments must approve the expenses entered by another department. We only want a single 'default' approver, but the way the data has been set-up and the query we currently use to fill in the approver returns multiple results.

In the tUserSec table, for example, we have two columns. Name(UserIDX) and UserCode

User1 - 550*

User2 - 55003*

The idea here being that User1 is the Director and so is a 'catchall' for everything in this department, while User2 is a Manager and is specifically assigned to a narrower division. Departments are always 7 characters total.

Say the Department is 5500309, the idea is that User2 should populate as the approver since their code is most closely matched to the Department ID. However, using the "Like" criteria returns both users and the form appears to select one of the two users at random with no rhyme or reason that I can determine. It always selects User1 for 5500309 but always selects User2 for 5500301, despite there being no further delineation - but ideally User1 shouldn't be populating at all unless no one else matches closer.

Below is a simplified version of the SQL, I cut out some other stuff that muddies the situation:

SELECT TDepts.Dept, TDepts.DDescr, tUserSec.UserIDX
FROM tUserSec, TDepts
WHERE (((TDepts.Dept) Like [usercode] & "*"));

How can I change this up so that I only pull in the UserID who is most like the usercode? I tried to figure out a way to pull in the UserID based on the length or max of the usercode, etc. but I wasn't able to find a way that worked. It's a safe assumption that if two users have usercodes that are "like" the department that the usercode that is longest is the one we want.

(This is my first question on here and a struggled with how to best explain this issue. Please be gentle :) )

Answer 1

First, I have to say that the main problem here is when a developer thought that they would be clever and build a lot of logic into the department and user IDs. Hiding this sort of information within a column is a big source of headaches in general (as you're just starting to see).

I don't develop with Access, so I'm not certain of the syntax, but hopefully you'll get the general idea. Please let me know if the syntax needs to be tweaked for future users who find this question:

SELECT
    D.Dept,
    D.DDescr,
    U.UserIDX
FROM
    TDepts D
LEFT OUTER JOIN
(
    SELECT
        SQ_D.Dept,
        MAX(LEN(SQ_U.usercode)) AS max_len_usercode
    FROM
        TDepts SQ_D
    INNER JOIN tUserSec SQ_U ON SQ_D.Dept LIKE SQ_U.usercode & "*"
    GROUP BY
        SQ_D.Dept
) SQ ON SQ_D.Dept = D.Dept
LEFT OUTER JOIN tUserSec U ON
    D.Dept LIKE U.usercode & "*" AND
    LEN(U.usercode) = SQ.max_len_usercode

The query gets a list of all of the departments along with the length of the longest usercode that matches for that department. Then it uses that to determine which user matches for the "most like" the department.