Searching Access Fields Using A Separate Table

0
votes

I have an Access Query which is built using 1 Raw Data Table to look at credit and cancellation charges.

I am trying to populate a 'Reason Code' field in this Query by searching the 'Description' field for various keywords.

For example, if the 'Description' field contains 'Error' then the 'Reason Code' field should populate 'Customer Error'.

If the 'Description' field contains 'Rebate' then the 'Reason Code' field should populate 'Customer Contractual Discount' and so on.

At the moment, I am doing this by using the below Expression:

*Reason_Code: IIf([Description] Like "Discount" Or [Description] Like "Free" Or [Description] Like "Rebate" , "Customer Contractual Discount",IIf([Description] Like "Error", "Customer Error",""))*

However, there are approx 100 keywords that could be found in the search with a different Reason Code for each keyword and hence using an Expression is not viable.

I want to therefore somehow reference another separate Table that contains the 100 Keywords and Reason Codes to do the search instead of writing expressions.

The problem is there will be no link between this 'Reason Code' table and the Main table (that the Query is based on) as the keywords will only ever be part of the 'Description' field of the Main Table and will never be an exact match, therefore I can't link the 2 in any way.

Is there a way to use a separate 'Reason Code' table with 2 Fields: (1) Keyword & (2) Reason Codes so that the Keyword field can be used to do the search/match in the Query and then return the relevant Reason Code for all fields where the keyword is found?

Thanks

2
ms-accessms-access-2010ms-access-2013ms-access-2016

2 Answers

0
votes

You can lookup information from different tables using DLookUp or subqueries. The preferred way in queries is using subqueries.

If you want to do it in the form of an expression, you could use something like this:

Reason_Code:(SELECT Reason_Code From OtherTable WHERE OtherTable.Keyword = MainTable.Description)
0
votes

You can use something like this:

Nz(DLookup("[ReasonCode]","[LooukUpTable]","'" & [Description] & "' LIKE '*[KeyWord]*'"),"")

Depending on where you use it (in SQL or in VBA), small changes may be necessary.