?
Solved

Access 2010 - Compare email and URL extensions

Posted on 2011-10-17
21
Medium Priority
?
260 Views
Last Modified: 2012-05-12
I have two tables created from a spreadsheet.  One has emails and the other has a URL.  There is for example johndoe@companyname.com in one table and www.companyname.com in a column in the other table.  They want to match any email ending in @companyname.com with a URL that is www.companyname.com.  How would an update query work to match them up?

Any help is appreciated,
John
0
Comment
Question by:JohnMac328
  • 9
  • 6
  • 6
21 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36979589

select t1.email,t2.url
where mid([email],instr([email],"@")+1)=mid([url],4)


0
 

Author Comment

by:JohnMac328
ID: 36979590
Actually I should have said a query using "Like" to get them to match up
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36979598


select t1.email,t2.url
where mid([email],instr([email],"@")+1)=mid([url],5)


0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36979616
you can also use this

select t1.email,t2.url
where mid([email],instr([email],"@")+1)=mid([url],instr([url],"www.")+4)
and [email] & ""<>"" and [url] & ""<>""
0
 

Author Comment

by:JohnMac328
ID: 36979623
I am getting a syntax error - missing operator

select email.email,RAIDatabasesFirms.Websites
where mid([email],instr([email],"@")+1)=mid([Websites],4)
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 36979641
You said an "update query", what are you trying to update?

In addition to using the WHERE clause, as capricorn1 has done, you could also use a non-equi join.  But this type of join is not visible in the query design window.

SELECT T1.Email, T2.URL
FROM T1
INNER JOIN T2
ON mid(T1.Email, instr(T1.email, "@") + 1) = mid(T2.URL, 5)
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 36979647
sorry, my fault (need coffee)

select email.email,RAIDatabasesFirms.Websites
FROM email, RAIDatabasesFirms
where mid([email],instr([email],"@")+1)=mid([Websites],5)
0
 

Author Comment

by:JohnMac328
ID: 36979648
Actually I should have said a query using "Like" to get them to match up - I do need the grid view if possible to do additional things
0
 

Author Closing Comment

by:JohnMac328
ID: 36979658
Great thanks.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36979663

test this

select email.email,RAIDatabasesFirms.Websites, mid([email],instr([email],"@")+1)
FROM email, RAIDatabasesFirms
where mid([email],instr([email],"@")+1) Like mid([Websites],5)
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 36979676
or use:

where [Websites] Like "*" & mid([email],instr([email],"@")+1)
0
 

Author Comment

by:JohnMac328
ID: 36981761
No matter what I do this operation locks up Access 2010.  I tried it in Access 2003 with the same result.  The amount of records the query is bringing back is about 7,500 so i don't see what the problem is.  I have programs running ten times that many records with no problems.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36981828
might be better to create a new field for both tables, "URLName"
then run an update queries

Update Email set [URLName]=mid([email],instr([email],"@")+1) Where [email] & ""<>""

Update RAIDatabasesFirms set [URLName]=mid([Websites],5) Where [Websites] & ""<>""


then create a query

select Email.*
From Email E inner join RAIDatabasesFirms R
On E.UrlName=R.UrlName


0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 36981848
which operation?

How many records in each of those two tables?  The way the query is written, using a WHERE clause instead of a JOIN will cause a Cartesian join, with the number of records in the basic recordset = (# in table A) * (# in table B).
The WHERE clause will filter those that don't meet the criteria, but it will initially develop a recordset as indicated above.

Is this against a SQL Server database, I remember seeing a similar question after you accepted the answer to this question that looked like it was pointing to SQL Server and was having difficulty with the instr() function.

What SQL are you using now?


0
 

Author Comment

by:JohnMac328
ID: 36981861
Actually i walked away to help someone and when I got back it finally finished the query without locking up.  I am guessing that no key field between these tables causes it to take a long time.  Thanks for the responses!
0
 

Author Comment

by:JohnMac328
ID: 36981957
I am getting for example johndoe@cs.com is matching up with www.companynamecs.com, it is grabing any pattern that matches.  Any way to start the matchup after the "www"?
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 36981994
Again, which query are you using?  You might try:

where [Websites] Like "*." & mid([email],instr([email],"@")+1)


0
 

Author Comment

by:JohnMac328
ID: 36982015
Sorry about that - here is the query
SELECT email.email, RIADatabaseFirm.Websites, Mid([email],InStr([email],"@")+1) AS Expr1 INTO MaxExport
FROM email, RIADatabaseFirm
WHERE (((RIADatabaseFirm.Websites) Like "*" & Mid([email],InStr([email],"@")+1)));

Open in new window

0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 36982101
Try adding the period (.) after the asterisk, as I indicated in my previous post.

Since you are just dumping these fields into a temporary table, try:

SELECT email.email, RIADatabaseFirm.Websites, Mid(email.[email],InStr([email],"@")+1) AS Expr1
INTO MaxExport
FROM email
INNER JOIN RIADatabaseFirm
ON RIADatabaseFirm.Websites Like "*." & Mid(email.[email],InStr(email.[email],"@")+1)));
0
 

Author Comment

by:JohnMac328
ID: 36982255
From what I saw from the results it looks like the period made the difference.  Many thanks
0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 36982272
still might want to try the non-equi join.

It is a good tool to have in your tool bag, if you understand how it works.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

840 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