MS Access query to search string for text from another table

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.
DrakeCAAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Commented:
test this


SELECT CRMData.*, RegistrationData.FullName
FROM RegistrationData, CRMData
WHERE CRMData.Subject Like '*" & RegistrationData.FullName & "*';

or

SELECT CRMData.*, RegistrationData.FullName
FROM RegistrationData, CRMData
WHERE Instr([CRMData].[Subject],[RegistrationData].[FullName])>0;
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Access uses the asterisk as the wildcard operator, so try this:

SELECT CRMData.*, RegistrationData.FullName
FROM RegistrationData, CRMData
WHERE RegistrationData.FullName Like '*" & CRMData.Subject & "*';

You may also need to Join those two tables. Your current query may not give you the correct results.

Are there any common fields between the two?
0
 
DrakeCAAuthor Commented:
Unfortunately there is no common field between the two files.  That's the reason I'm trying to search for the full name info within the string.

I tried using * as the wild card but it didn't bring any results.
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Does your CRM output data in this exact format:

Seminar Name - Company Name - Contact Name, Contact Title

Including the dashes and such?

And which of those data elements are you trying to compare? The Company Name, Seminar Name, etc?
0
 
DrakeCAAuthor Commented:
Unfortunately the data entry clerk keyed all data into one field, labelled Subject.  She also wasn't consistent in how she keyed it in.  Most of them have data entered the way it is listed above (with dashes) but other items don't have the date, some don't have title etc.

All of the subject lines do have a Contact Name listed somewhere in the Subject field.  I need to match that with the FullName field in my Registration table.
0
 
DrakeCAAuthor Commented:
Awesome!  The second option works great.

Thanks for the assistance.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.