Solved

MS Access query to search string for text from another table

Posted on 2014-04-25
6
2,491 Views
Last Modified: 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.
0
Comment
Question by:DrakeCA
  • 3
  • 2
6 Comments
 
LVL 84
ID: 40022983
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
 

Author Comment

by:DrakeCA
ID: 40022990
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
 
LVL 84
ID: 40023029
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:DrakeCA
ID: 40023040
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
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 40023174
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
 

Author Closing Comment

by:DrakeCA
ID: 40023484
Awesome!  The second option works great.

Thanks for the assistance.
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now