Sleeper: Joining Tables with a Range and Wildcards values

[Jay82]

Morning gurus

I'm stumped and need some help with a query join.

I have a table called "Role_Transactions" (its from SAP) and for each "Role", it has multiple "TCode_From" and "TCode_To" values. Example below;
Roles.jpg

I need to match all the "TCodes" from table "Transactions" to the "TCode_From" and "TCode_To" values above, and display both "Role" and "TCode" in the output.
Transactions.jpg

Here's where it gets tricky for me;
- If only "TCode_From" is populated ("TCode_To" is null), then the match is exact between the two tables.
- If there is a value in "TCode_To", that indicates all "TCodes" within the range should be matched.
- Sometimes "TCode_From" and "TCode_To" have a * for a wildcard in them too. Indicating that only the "TCodes" between the two ranges should be matched.

I need "Role" and "TCode" to be output.

Any help would be greatly appreciated!

Cheers!

[arnelgp]

see if the FinalQuery is what you need.
also view the "smaller queries".
they are made according to your criterias.

[June7]

Cross-post with solution https://www.access-programmers.co.uk.../#post-1865409

[Aussiebear]

It seems Jay82 is a prolific cross poster. Also at Utter Access as well.

[Jay82]

I'm new to the forum space and didn't know ''cross-posting" was a thing until it was mentioned today. First time I've ever done it so good thing is now I'm aware its should be avoided in the future... I came back to this post to link the solution, but June7 beat me to it. My apologies!

[Aussiebear]

Common rule amongst almost the forums that I know.

[Jay82]

Your 4k posts to my 2. I've taken this onboard ��

[Gasman]

If you mention you have crossposted and supply links to where, then that generally is OK.?

Just stops people wasting time duplicating the same or similar advice.

That is why it is frowned upon when not notified.

As you say, lesson learnt.