2 Table Query matching similar fields

[newestAF]

I've done some searching for this answer and only found similar ones but nothing that would seem to really help me. I have 2 tables [TblReport] & [TblAccounts]. The accounts table includes a field called agency and the report table has a similar field called case status. Case status has the agency inside the data but also includes "referred to" or "action completed", etc at the beginning. I can't change that. That's the way I receive it. I need to link the fields together to ID who to send the reports to. The accounts table has e-mails associated to the agency field. I'm assuming some time of Trim but not familiar with using it. Agency names vary in length and with the spacing. I have absolutely no idea where to start. Could really use some help.

 

[Golom]

Select ... Some fields ...

From tblReport R, tblAccounts A

Where Instr(R.[Case Status], A.Agency) > 0

 

[dhookom]

Please provide:
- actual table and field names and data types
- sample records
- desired relationships/joins

Duane
Hook'D on Access
MS Access MVP

 

[newestAF]

Here you go:

table names are CMS Accts and CMS Report
field names are Agency and Case Status
data types are:
Agency: Base AD Customer Service Section
Case Status: Referred to Base AD Customer Service Section
Or: Action Completed/Base AD Customer Service Section
Or: Follow by 01-Oct-2009/Base AD Customer Service Section

 

[dhookom]

Try

SELECT ....
FROM [CMS Accts],[CMS Report]
WHERE [Case Status] Like "*" & [Agency] & "*";

Duane
Hook'D on Access
MS Access MVP

 

[newestAF]

Works like a charm. Thank you.