MS Access query to search string for text from another table
Posted on 2014-04-25
I was trying to complete this task in Excel but determined it wouldn't work. I imported into Access, hoping that would make things easier, but I am still having issue.
Have a registrants table with field named FullName. I have data output report from our CRM with a field named Subject. This field contains a string of information in the form of "Seminar Name - Company Name - Contact Name, Contact Title".
I want to sort through the Subject field and find any records in this table which have a matching record in the Registrants table. I was planning on using the FullName field from that table to match on.
I tried using the following query but am having no luck. I assume I need a better query than this:
SELECT CRMData.*, RegistrationData.FullName
FROM RegistrationData, CRMData
WHERE RegistrationData.FullName Like '%" & CRMData.Subject & "%';
I have tried the same query, dropping the ampersands but still have no luck.
I am using Access 2003. Yes I know it's old but it's all I have access to right now.