Solved

MS Access query to search string for text from another table

Posted on 2014-04-25
6
2,273 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

by:DrakeCA
Comment Utility
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 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
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
Comment Utility
Awesome!  The second option works great.

Thanks for the assistance.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

728 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

10 Experts available now in Live!

Get 1:1 Help Now