Query to return most common values

[accesshelp]

Hi,

I wish to query a database table to return the most common email address domains.

So for example for the email address myname@mydomain.com, it would need to ignore the myname@ and just look at mydomain.com

Then return a list of the most popular end domains in the database and display them, most common first..

Is this possible? If so I would really appreciate some help with this!

Thanks so much

 

[MSAccessRookie]

Hi,

I wish to query a database table to return the most common email address domains.

So for example for the email address myname@mydomain.com, it would need to ignore the myname@ and just look at mydomain.com

Then return a list of the most popular end domains in the database and display them, most common first..

Is this possible? If so I would really appreciate some help with this!

Thanks so much

Take a look at the LIKE command. I believe it could provide what you are looking for.

 

[SOS]

Actually, you can split off the domain in a query and then do a count query on that.


To split off the domain you would use the MID function to find the @ sign and go from there. See if you can get and if not, I'll post a sample.

 

[John Big Booty]

You could also use a Totals query and use the following;

Right([EmailAdd],(Len([EmailAdd])-InStr(1,[EmailAdd],"@")))

to return the domain, which you could then Group By and Count.

 

[MSAccessRookie]

Wouldn't it be easier to just say something like the following?

Where email LIKE "*@mydomain.com"
​

 

[John Big Booty]

I understood the OP to mean that he wanted to find the most common @mydomain.com where he has used mydomain.com to represent any number of possible domains.

 

[SOS]

Wouldn't it be easier to just say something like the following?

Where email LIKE "*@mydomain.com"
​

Not if they are looking to get counts of EACH domain listed. (think about it, they would have to type in each domain?)

 

[MSAccessRookie]

I understood the OP to mean that he wanted to find the most common @mydomain.com where he has used mydomain.com to represent any number of possible domains.

In that case, I would have to stand corrected. I guess I was thinking something different. The OP of course knows best what the intention was.

 

[SOS]

But yes, if they were looking for a specific one, that would have been good.

 

[MSAccessRookie]

Not if they are looking to get counts of EACH domain listed. (think about it, they would have to type in each domain?)

You have a good point. I guess I was thinking something different. The OP of course knows best what the intention was.

 

[accesshelp]

hi john, yes that is exactly right. Im trying to figure something out from these bit here, but struggling. I know nothing about access at all. I tried you query and it returned some stuff, but doesnt appear to be what i need.

The results should look like this (> indicating next collum in same row):

hotmail.com > mike P
hotmail.com > jason C
hotmail.com > ollie e
hotmail.com > sarah h
aol.com > dave t
aol.com > simon c
aol.com david k
yahoo.com > kaeron K
yahoo.com > garry T
live.com > barrack j

etc. I need the surrounding info, and the domain popularity to be the lead for ordering the results.

It would also be interesting to see how to just list them according to most popular whole email address including everything before the @. This would be to check for duplicates, and see the most popular duplicates.

Thanks everyone for you help. I hope I have explained a bit clearer.