MS Access: How to use value derived from subquery in LIKE condition with wildcards?-CodePudding

In MS Access I have a query that looks like this:

SELECT * FROM Budget WHERE Budget.[EXPENSE_NAME] Like '*Temps/Seasonal Employees*'

This finds over 40 matches.

There is a category table with category display name Temps/Seasonal Employees which gets used to generate the longer EXPENSE_NAME of a Budget record. To improve the query by avoiding wonky-looking, hardcoded changeable stings, I'd rather use a more consistent code name. (The display name was actually changed recently, requiring several queries to be updated.)

The wonky-looking string Temps/Seasonal Employees can be acquired by looking up the code name via:

SELECT COST_DISPLAY FROM CostType WHERE CODE_NAME = 'TEMP_EMPLOYEE'

This finds exactly one match, with one column, thus one string, namely Temps/Seasonal Employees.

It seems logical that I could substitute the second query into the first one for Temps/Seasonal Employees via:

SELECT * FROM Budget WHERE Budget.[EXPENSE_NAME] Like 
'*' & (SELECT COST_DISPLAY FROM CostType WHERE CODE_NAME = 'TEMP_EMPLOYEE') & '*'

This finds zero matches. Likewise if move the wildcards into the subquery:

SELECT * FROM Budget WHERE Budget.[EXPENSE_NAME] Like
(SELECT '*' & COST_DISPLAY & '*' FROM CostType WHERE CODE_NAME = 'TEMP_EMPLOYEE')

I realized that Access might be seeing the subquery results as a list of one item instead of a string, so I tried the last two queries with TOP 1 in the subquery, but that made no difference.

Is there another way to do this, or is what I'm trying to accomplish not possible?

CodePudding user response：

This seems overly complex and I doubt if it would work.

In such case I would join the two tables (not validated in Access):

SELECT
    Budget.*
FROM
    Budget
    inner join CostType on CostType.COST_DISPLAY = Budget.[EXPENSE_NAME]
WHERE
    CostType.CODE_NAME = 'TEMP_EMPLOYEE'

But if CostType.CODE_NAME is the key in CostType table then it would be better to change data model by adding a column Budget.CODE_NAME in Budget with a 1:n relationship, and then remove Budget.[EXPENSE_NAME].

CodePudding user response：

It is should work, only one record must be returned, and I would expect the concatenating to go like this:

SELECT * FROM Budget 
WHERE Budget.[EXPENSE_NAME] Like
    (SELECT TOP 1 "'*" & COST_DISPLAY & "*'" FROM CostType 
    WHERE CODE_NAME = 'TEMP_EMPLOYEE')