Conditional Relationship between Tables (Mapping Table) (1 Viewer)

[ProgAcc]

Hello, in practice it is a bit more complicated but I simplify the example to get to the core principle of the problem.
I am trying to link HR team codes to budget codes. The problem is, the hierarchies are different.
For example the HR Code could be IT-F, with multiple sub-teams IT-FA, IT-FB, IT-FC etc.
Now, in practice OFTEN-times (not always) the first letter after the minus has an exact relationship to the budget code, e.g. IT-F should be mapped to Budget-A (so including all of the different sub-codes IT-FA etc.). In such cases I want to have a one-to-one relationship. The problem is that sometime we need to differentiate. For example, it could be that IT-GB goes to Budget-B, but IT-GC to budget C, and then all the rest of the IT-GX-codes go to Budget-A.
In Excel I have solved this problem of first checking if there is a more specific budget allocation before going to the more general one with a mapping table (in the MaDa-tab):
=IFNA(VLOOKUP(LEFT(M4471,6),MaDa!A:C,3,FALSE),IFNA(VLOOKUP(LEFT(M4471,5),MaDa!A:C,3,FALSE),VLOOKUP(LEFT(M4471,4),MaDa!A:C,3,FALSE))
The MaDa table contains the rules in the following way:
IT-F >>> Budget-A
IT-G >> Budget-A
IT-GB >> Budget-B
IT-GC >> Budget-C
[...]

With this method I do not have to have ALL of the hundreds of sub-codes (because they actually go further, e.g. IT-GAC-FFK etc.) but rather I only look at the level of detail necessary and if nothing is specified for that length it goes to the shorter code.
Now I don't know how I could do this the most efficient way in Access. A relationship does not work, because I would have to have either multiple extra columns with decreasing number of LEFT-characters and even then the more specific ones would also match the more general, something that is avoided in the Cascaded IF-Formula in Excel. I thought about using DLOOKUP in Access but read in this forum that it is "inefficient" and nto the "smart-way".
So I am asking, what would be the best solution for solving my problem in Access?

Thanks in advance.

 

[arnelgp]

put those code (IT-F, IT-G, etc to a table and add another column for the corresponding budget center).

youll still need to use the ineffective dlookup:

IIF(IsNull(dlookup("budgetCenter", "table", "code='" & theCode & "'")), dlookup("budgetCenter", "table", "code='" & Left(theCode, 3) & "'"))

 

[ProgAcc]

Thanks for the reply. I tested this approach. It works, but it is as slow as in Excel via VLOOKUP. I am looking for an approach that is faster. Shouldn't a database setup be able to help?

 

[plog]

I think you're overcomplicating it trying to find partial logic to the code determinations. Throw away all your rules of thumb and just build a table that maps them.

Does the "IT" portion of "IT-GC" denote data itself? Do those letters ever change? If so, you need to house them in their own field in this table.