Help eliminating multiple results based on field length

[Dastion]

We have a form users fill out to charge other departments, each department has an 'approver' for these circumstances. However, I'm running in to an issue in applying the proper approver based on how the original creator of this DB set everything up.

We use two tables:

tDepts - a list of all of our departments. All departments are 7 characters long. (i.e. 5500309 - Athletics). Fields are Dept and DDescr (DeptID# and dept description).


tUserSec - a table of Usernames and their security levels over departments. The fields are UserIDX and UserCode. Usercode is a prefix that is compared to DeptID. For example:
User1 - 550*
User2 - 55003*
(User1 is a director while User2 is a Manager who is delegated a specific division.)




We use a select query that pulls in Dept, DDescr, and UserIDX. The only criteria is under DeptID which states Like [UserCode] & "*"

The problem I run in to is that, in the example given for 5500309, it populates a row for both User1 and User2 since both have Usercodes which are "like" 5500309. I'm trying to figure out a way to only pull in the User whose code is most-like the DeptID or, basically, the user who has the longest UserCode.

This seems like it should be easy, but i can't wrap my head around it.

The resulting code is:

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

[RuralGuy]

Would not using Like but looking for an exact match work here?

[CJ_London]

I suspect you would find it a lot easier if your usercode was split into it's different components - what happens if you have two usercodes of the same length? but you can try something like

SELECT TDepts.Dept, TDepts.DDescr, tUserSec.UserIDX
 FROM TDepts INNER JOIN tUserSec ON TDepts.Dept Like tUserSec.[usercode] & "*"
WHERE Len(usercode)=(SELECT Max(len(usercode)) FROM tUserSec AS T WHERE TDepts.Dept Like T.[usercode] & "*")

Note: because of the join, this cannot be displayed in the query window, only the sql window

this might work instead and will show in the query window

SELECT TDepts.Dept, TDepts.DDescr, tUserSec.UserIDX
FROM tUserSec, TDepts
WHERE TDepts.Dept Like [usercode]&"*" AND  Len(usercode)=(SELECT Max(len(usercode)) FROM tUserSec AS T WHERE TDepts.Dept Like T.[usercode] & "*")

[Dastion]

Ajax - both of your answers worked very well, however something I didn't mention is that this is for a drop-down list in a form and the extra bit caused some serious calculation when the drop down was selected.

However, you did help me stumble upon a simple, if inelegant, solution. I added the usercode as a field and then set the query results to sort by DeptID Ascending then Usercode Descending. The form just grabs the first instance of the value to appear in the table so this made the user with the longest usercode populate. For example my results now populate as:

5500309 - Athletics - User1 - 55003*
5500309 - Athletics - User2 - 550*


Thanks for your help.

[CJ_London]

thought this was for all departments - does make a difference if you are using a combo

I would have suggested

SELECT TOP 1 TDepts.Dept, TDepts.DDescr, tUserSec.UserIDX
FROM tUserSec, TDepts
WHERE TDepts.Dept Like [usercode] & "*"
ORDER BY Len(usercode) desc

but then why would you want to use a combo to display just one record?

[RuralGuy]

Would you like to mark this as solved yet?

[Dastion]

Sorry yes - it should be done now. I'm going to mess with Ajaxx's latest response (thanks again by the way) but in either case I have a solution that works for me.


Edit:
Ajaxx - to answer your question it is a drop-down for all departments. When you select or key in a dept to the combo box it fills in the 'approver' field based on the department chosen. The problem is that the way the usercode field was set-up more than one person's code was "like" the deptid (i.e. both User1 and User2s codes in the example). So the form would populate one or the other when, really, we needed to populate the one that most closely matches it (or, the longer one, in this case). So all departments, but only one approver.

The way my workaround works still populates both users, but since I sort the Usercode descending the more specific/longer usercode shows up first and so is chosen as the default approver. The whole DB can be a bit of a muddled mess sometimes, but it's an inherited beast of a DB that, though not overly efficient, saves a lot of paperwork in the end.

[RuralGuy]

Cool. Thanks for marking this thread as Solved.