create query to display UNIQUE email address

[DocWebb]

I tried this to produce a list of UNIQUE email addresses out of our ms access db (tablename db fields include email, list and date):

SELECT DISTINCT tablename.email, *
FROM tablename
WHERE (((tablename.email) Is Not Null) AND ((tablename.list) Like "yes"))
ORDER BY tablename.date;

BUT I am still getting duplicate emails address in the list.

Any suggestions???

Thanks.

Doc

 

[John Big Booty]

Try the Group By option of a Totals Query.

 

[plog]

If you just want unique emails why are you bringing in every field?

SELECT DISTINCT tablename.email, *

The * is screwing your query up because I believe it applies to every field listed, not just the one it precedes. If you just want unique email addresses I would use this query:

SELECT email FROM tablename GROUP BY email;

 

[DocWebb]

THANKS! THAT HELPED!!!

This works;

SELECT tablename.email, tablename.firstname, tablename.lastname, FROM tablename
GROUP BY tablename.email, tablename.firstname, tablename.lastname, HAVING (((tablename.email) Is Not Null));

Sorry I forgot to say I need first and last names too, that is why I used * to include the other fields. ELIMINATED THE * AND USED SPECIFIC FIELD NAMES

HOWEVER NOW I AM GETTING NULL EMAIL ADDRESSES IN MY LIST EVEN THOUGH I USED;

HAVING (((tablename.email) Is Not Null)); HMMMM...

Doc

If you just want unique emails why are you bringing in every field?

SELECT DISTINCT tablename.email, *

The * is screwing your query up because I believe it applies to every field listed, not just the one it precedes. If you just want unique email addresses I would use this query:

SELECT email FROM tablename GROUP BY email;

 

[DocWebb]

OOPS - IT IS NOT WORKING. STILL HAVE DUPLICATE EMAILS IN MY LIST!

Doc

If you just want unique emails why are you bringing in every field?

SELECT DISTINCT tablename.email, *

The * is screwing your query up because I believe it applies to every field listed, not just the one it precedes. If you just want unique email addresses I would use this query:

SELECT email FROM tablename GROUP BY email;

 

[plog]

When you bring in and GROUP BY fields other than your email field you are going to have duplicate emails. One way around this is to not GROUP BY those other fields but use the Min, Max or First. This will keep your emails unique and select just the corresponding values (Max, Min, First) for those other fields.

 

[DocWebb]

PREFECT! THANKS!

This works;

SELECT tablename.email, Min(tablename.firstname) AS MinOffirstname, Min(tablename.lastname) AS MinOflastname
FROM tablename
GROUP BY tablename.email
HAVING (((tablename.email) Like "*@*" And (tablename.email) Not Like "* *" And (tablename.email) Not Like " *"));

Following addition to query is used to filter out some blank or bad email addresses:

HAVING (((tablename.email) Like "*@*" And (tablename.email) Not Like "* *" And (tablename.email) Not Like " *"));

Doc

When you bring in and GROUP BY fields other than your email field you are going to have duplicate emails. One way around this is to not GROUP BY those other fields but use the Min, Max or First. This will keep your emails unique and select just the corresponding values (Max, Min, First) for those other fields.