duplicate telephone numbers in daytime, evening, how do I clear 2n

[Guest]

I have a query that I have a set of telephone numbers. Within the query there
is a tel1, tel2 & tel3 result. Sometimes the tel1 has the same value as tel2.
What I want to do in this situation is remove the duplicate text in the tel2
column but not delete the record

 

[Guest]

The problem really stems from the design of the table. Instead of having
multiple telephone number columns a better design would be to have each
telephone number as a row in a related table, which is in turn related to a
NumberTypes table via another table which models the many-to-many
relationship between TelephoneNumbers and NumberTypes. You can then have as
many or as few telephone numbers per person as necessary, and each number can
be classified as one or more types.

However, with the current design you can suppress the duplicates in a query
like so:

SELECT ContactID, tel1 AS [Primary Number],
IIF(tel2=tel1,"",tel2) AS [Alternative Number],
tel3 AS Mobile
FROM Contacts;

or to permanently remove the duplicated values in tel2 execute an update
query:

UPDATE Contacts
SET tel2 = NULL
WHERE tel2=tel1;

Ken Sheridan
Stafford, England

 

[Marshall Barton]

I have a query that I have a set of telephone numbers. Within the query there
is a tel1, tel2 & tel3 result. Sometimes the tel1 has the same value as tel2.
What I want to do in this situation is remove the duplicate text in the tel2
column but not delete the record

You can permantly remove (set to Null) a field's value by
using an Update query:

UPDATE table SET tel2 = Null WHERE tel2 = tel1

If you do not want to change the values in the table, then
use a Select query with a calculated field, to retrieve the
records:

SELECT tel1, IIf(tel2 = tel1, Null, tel2) As ph2, tel3
FROM table