Solved Need Help - Joining Tables with a Range and Wildcards values (1 Viewer)

[Jay_1982]

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;

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.

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!

 

[GPGeorge]

Cross posted at UtterAccess.

 

[plog]

The best, clearest way to communicate data needs is via data. Please provide 2 sets:

A. Starting data from your table(s). Include table name and enough data to cover all cases.

B. Expected results of A. Show us what you hope to end up with when you start with the data from A.

Again, 2 sets of data--starting and expected results.

 

[Jay_1982]

Hey @plog , attached is some sample dataset and the expected output.

 

[plog]

Wow. You completely nailed what I asked for and it made things crystal clear and helped me have data to test with. Thank you and great job. Search through my post history, I always ask for A & B data and it always takes 3 rounds of back and forth to get the original poster to give me what I want, so frustrating, but you nailed it.

The big problem is that wildcards and comparisons don't work well together. So my strategy was to eliminate the wildcards with a definitive value and set an specific range for every record in Role_Transactions. So, I created a sub-query that generated those begin and end values:

SELECT Role_Transactions.Role, Role_Transactions.TCode_From, Role_Transactions.TCode_To, Replace([TCode_From],"*","") AS BeginCode, IIf(IsNull([TCode_To]),[TCode_From],Replace([TCode_To],"*","{")) AS EndCode
FROM Role_Transactions;

Paste that into a new query and name it 'subTransactionMatch' and you will see those 2 fields (BeginCode & EndCode). You always have a beginning code and if the ending code was blank, I just copied the begining code to it. If the ending code used a wild card I replaced the wildcard with '{' which when sorting by text comes after all the alphanumeric characters, That way it will catch everything you want in the range. The next step was to JOIN that query with Transactions to get the results you want:

SELECT subTransactionMatch.Role, Transactions.Tcode
FROM subTransactionMatch INNER JOIN Transactions ON (subTransactionMatch.EndCode >= Transactions.Tcode) AND (subTransactionMatch.BeginCode <= Transactions.Tcode);

Paste that into a query and run it. Because you are joining tables on a range of values I had to hand edit the JOIN so that it could use the >= and <= comparisons. Because of this, the Access Query Designer can't handle the query, it only knows equi-joins (using the = sign). So you can't edit it with the Designer and it will give an error message if you try and open it with it. It's still valid SQL and does what you want, you'll just have an issue editing that final query.

If you find an issue, please give me the data you are having an issue with so I can test see and test it.

 

[Jay_1982]

Wow indeed! Your solution was both elegant and absolutely spot on!!!

Appreciate how how you've taken the time to explain what each of the queries does too, that's a huge help to a novice such as myself.

Thank you so much @plog !