Solved

MS Access query to search string for text from another table

Posted on 2014-04-25
6
2,565 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
The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

766 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