Solved

MS Access query to search string for text from another table

Posted on 2014-04-25
6
2,772 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 85
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 85
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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 start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

707 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