Excluding Duplicates in One Field

[Rob B.]

Hi,

I'm stuck. I have two fields, fName and fEmail. Many of the fEmail
fields have duplicates because often people in the same house use the
same e-mail address. I need to query the data so the result does not
contain the duplicate e-mail addresses. For some reason, "unique values"
still pulls up the duplicates.

I know if I exclude one of the duplicate e-mail addresses, then I also
end up exluding the name in the same record, but so long as the output
has at least one of the names, along with every record for which there is
a unique e-mail address, that is OK for my purposes.

Thank you for expending gray matter on my behalf.


Rob B.

 

[Tom Wickerath]

Hi Rob,

Try something like this:

SELECT First(fName) AS FirstName, fEmail
FROM {TableName}
GROUP BY fEmail
HAVING fEmail Is Not Null
ORDER BY First(fName);


where {TableName} is the name of the table.

Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[John W. Vinson]

Hi Rob,

Try something like this:

SELECT First(fName) AS FirstName, fEmail
FROM {TableName}
GROUP BY fEmail
HAVING fEmail Is Not Null
ORDER BY First(fName);

Just one (probably trivial in this case) suggestion: both HAVING and WHERE
filter records, but HAVING does so *after* all the records have been
retrieved, sorted, grouped, and summed etc. I'd use

SELECT First(fName) AS FirstName, fEmail
FROM {TableName}
GROUP BY fEmail
WHERE fEmail Is Not Null
ORDER BY First(fName);

 

[Tom Wickerath]

Hi John,

I get a syntax error when I try to substitute the WHERE keyword for the
HAVING keyword.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

Oh, you need to switch the order of the GROUP BY and WHERE clauses:

SELECT First(fName) AS FirstName, fEmail
FROM {TableName}
GROUP BY fEmail
WHERE fEmail Is Not Null
ORDER BY First(fName);

Needs to be:

SELECT First(fName) AS FirstName, fEmail
FROM {TableName}
WHERE fEmail Is Not Null
GROUP BY fEmail
ORDER BY First(fName);


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html

 

[John W. Vinson]

Oh, you need to switch the order of the GROUP BY and WHERE clauses:

EEP!

Tom, we've discovered the Access Correlary to the Spelling Flame Law of Usenet
(any spelling flame will contain an unnoticed spelling error).

What's funny is that I just answered another post about incorrect ordering of
the where clause...

 

[NATALIYAGEORGIY]

Hi,

I'm stuck. I have two fields, fName and fEmail. Many of the fEmail
fields have duplicates because often people in the same house use the
same e-mail address. I need to query the data so the result does not
contain the duplicate e-mail addresses. For some reason, "unique
values"
still pulls up the duplicates.

I know if I exclude one of the duplicate e-mail addresses, then I also
end up exluding the name in the same record, but so long as the output
has at least one of the names, along with every record for which there
is
a unique e-mail address, that is OK for my purposes.

Thank you for expending gray matter on my behalf.


Rob B.

 

[Rob B.]

Thanks to all on their responses. I got what I asked for. Unfortunately, I
was trying to be a minimalist, just providing the bare bones information of
what I needed, thinking I could add other fields to the SQL once I found out
how to get it to only return one record when there were duplicate email
addresses.

So, now here's the problem. I added another field to the query, fldAddress.
I am being minimalist, again. There's other fields I want to add, too, but
am hopeful I might be able to take the answer to this question and apply it
to the other fields without making too much a bother of myself here.

SELECT First([fldNameL] & " " & [fldNameF] & " " & [fldNameI] & " " &
[fldNameSuffix]), fldEmail
FROM tAllRecs
WHERE fldEmail Is Not Null
GROUP BY fldEmail
ORDER BY First([fldNameL] & " " & [fldNameF] & " " & [fldNameI] & " " &
[fldNameSuffix]);

Based on your responses, that does what I asked for. The problem is when I
add a field that is unique for every record the duplicates come back.

In the below, adding fldAddress and fldPhHome didn't break the desired one
unique email address output. I am guessing it is because if there is more
than one record with the same e-mail address shared within a family, then it
is likely the family's address and phone number fields are also the same.
The problem of displaying identical email addresses comes back, though, when
I try to add a field I know is unique to each record, fldMbrNo. For
example:

SELECT First([fldNameL] & " " & [fldNameF] & " " & [fldNameI] & " " &
[fldNameSuffix]) AS Expr1, tAllRecs.fldEmail, tAllRecs.fldAddress,
tAllRecs.fldPhHome, tAllRecs.fldMbrNo
FROM tAllRecs
WHERE fldEmail Is Not Null
GROUP BY tAllRecs.fldEmail, tAllRecs.fldAddress, tAllRecs.fldPhHome,
tAllRecs.fldMbrNo
ORDER BY First([fldNameL] & " " & [fldNameF] & " " & [fldNameI] & " " &
[fldNameSuffix]);

In case you are wondering why I am doing this, our members are being added
to an opt-in e-mail newsletter that charges based upon the number of e-mails
sent per month. We do not want to be sending multiple e-mails to the same
e-mail address because it drives up the cost per e-mail.


Rob B.

 

[Tom Wickerath]

Hi Rob,

In the below, adding fldAddress and fldPhHome didn't break the desired one
unique email address output. I am guessing it is because if there is more
than one record with the same e-mail address shared within a family, then it
is likely the family's address and phone number fields are also the same.

Correct. However, if the addresses and/or phone number were not entered
exactly the same, then you'd still get duplicate results. For instance, these
two addresses are the same to a human, but not to a computer:

1234 Main Street vs. 1234 Main St. (or 1234 Main St [St without a period])

Likewise with phone numbers:
555-123-4567 is not the same as (555) 123-4567 or 555 123-4567.

The problem of displaying identical email addresses comes back, though, when
I try to add a field I know is unique to each record, fldMbrNo.

This is an expected result. Try the idea of using a subquery. Access MVP
Allen Browne has a good page on using subqueries, here:

Subquery basics
http://allenbrowne.com/subquery-01.html


Post back if you need help working it out.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Thanks to all on their responses. I got what I asked for. Unfortunately, I
was trying to be a minimalist, just providing the bare bones information of
what I needed, thinking I could add other fields to the SQL once I found out
how to get it to only return one record when there were duplicate email
addresses.

So, now here's the problem. I added another field to the query, fldAddress.
I am being minimalist, again. There's other fields I want to add, too, but
am hopeful I might be able to take the answer to this question and apply it
to the other fields without making too much a bother of myself here.

SELECT First([fldNameL] & " " & [fldNameF] & " " & [fldNameI] & " " &
[fldNameSuffix]), fldEmail
FROM tAllRecs
WHERE fldEmail Is Not Null
GROUP BY fldEmail
ORDER BY First([fldNameL] & " " & [fldNameF] & " " & [fldNameI] & " " &
[fldNameSuffix]);

Based on your responses, that does what I asked for. The problem is when I
add a field that is unique for every record the duplicates come back.

In the below, adding fldAddress and fldPhHome didn't break the desired one
unique email address output. I am guessing it is because if there is more
than one record with the same e-mail address shared within a family, then it
is likely the family's address and phone number fields are also the same.
The problem of displaying identical email addresses comes back, though, when
I try to add a field I know is unique to each record, fldMbrNo. For
example:

SELECT First([fldNameL] & " " & [fldNameF] & " " & [fldNameI] & " " &
[fldNameSuffix]) AS Expr1, tAllRecs.fldEmail, tAllRecs.fldAddress,
tAllRecs.fldPhHome, tAllRecs.fldMbrNo
FROM tAllRecs
WHERE fldEmail Is Not Null
GROUP BY tAllRecs.fldEmail, tAllRecs.fldAddress, tAllRecs.fldPhHome,
tAllRecs.fldMbrNo
ORDER BY First([fldNameL] & " " & [fldNameF] & " " & [fldNameI] & " " &
[fldNameSuffix]);

In case you are wondering why I am doing this, our members are being added
to an opt-in e-mail newsletter that charges based upon the number of e-mails
sent per month. We do not want to be sending multiple e-mails to the same
e-mail address because it drives up the cost per e-mail.


Rob B.