query on duplicates

[Maggie]

I want to make a query to pull all people with fax numbers. I can do this
but I have duplicate fax numbers. Is there a way to only pull one fax number
not the duplicate ones? If so how would I do that? Thanks in advance.

Maggie

 

[Jerry Whittle]

Are you saying that there are individuals with more than one fax number OR
that more than one person could be using the same fax number? Or, gulp, both?

 

[Maggie]

Jerry,

Unfortunately yes to both questions.

Maggie

 

[Jerry Whittle]

Ouch. I doubt if you'll get exactly what you want, but below are some things
that might help.

All the Fax Numbers assuming that they are entered the same way such as
555-555-5555. (555) 555-5555 will probably show up as a different number.

SELECT DISTINCT FaxNumber
FROM YourTable
ORDER BY 1 ;

All the people with fax numbers:

SELECT People, FaxNumber
FROM YourTable
WHERE FaxNumber Is Not Null
ORDER BY 1, 2 ;

All the people with more than one fax number and two of those numbers:

SELECT People,
Min(FaxNumber),
Max(FaxNumber),
Count(FaxNumber)
FROM YourTable
WHERE Count(FaxNumber) > 1
GROUP BY People
ORDER BY 1 ;

 

[Jerry Whittle]

Ouch. I doubt if you'll get exactly what you want, but below are some things
that might help.

All the Fax Numbers assuming that they are entered the same way such as
555-555-5555. (555) 555-5555 will probably show up as a different number.

SELECT DISTINCT FaxNumber
FROM YourTable
ORDER BY 1 ;

All the people with fax numbers:

SELECT People, FaxNumber
FROM YourTable
WHERE FaxNumber Is Not Null
ORDER BY 1, 2 ;

All the people with more than one fax number and two of those numbers:

SELECT People,
Min(FaxNumber),
Max(FaxNumber),
Count(FaxNumber)
FROM YourTable
WHERE Count(FaxNumber) > 1
GROUP BY People
ORDER BY 1 ;

 

[Maggie]

hi,

I will give it a try. I'll let you know how I make out. Thank you so much.

Maggie