Query specific data

  • Thread starter Thread starter hoachen
  • Start date Start date
H

hoachen

Using Access 2003,XP OS

I am trying to run a query from a table (See the data below). I want to run
a query that check the phone#, if the phone number is the same then i want to
eliminate the phoneType where is equal to "Cellphone".

I used the query here but not working due to multiple fields have the same
input.

SELECT distinct Sheet1.Phone#, Sheet1.Fname, Sheet1.LName, Sheet1.Dept#,
Sheet1.Address, Sheet1.PhoneType, Sheet1.ClassNumber
FROM Sheet1;
WHERE Sheet1.PhoneType = "Cellphone"

I searched the google and found some said that access does not support
distinct clause!

Please help!

Fname Lname Dept# Phone# Address PhoneType ClassNumber

Susan Linsey 890123 123-456-789 456 ABC Street Home MTK123
Susan Linsey 567880 123-456-789 456 ABC Street Cellphone MTK123
Dana Choi 232111 447-123-456 123 DEF Drive Home STkk1
Megan Winer 454646 447-123-457 678 AAA Ct Home JKTF199
Christina Pope 453455 447-123-458 890 North Road Home LLL77
Mike Scott 565656 447-123-459 888 S Court Home MNS68
David Nicole 654656 677-333-456 1111 Harlen St Cellphone PO90
Bob Smith 511345 888-900-123 1202 Can Do Home HBC56
Bob Smith 907896 888-900-123 1202 Can Do Cellphone HBC56
 
"if the phone number is the same" - same as what?

I'm unsure what you are trying to do but all you have to do is to type
DISTINCT into Access help and you will find that it does exist and fulfills a
very specific purpose. I dont see how that clause will help you though.

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".
 
One way would be to use a UNION Query

SELECT *
FROM Sheet1
WHERE PhoneType <> "CellPhone"
UNION
SELECT *
FROM Sheet1
WHERE Not Exists
(SELECT *
FROM Sheet1 as Temp
WHERE PhoneType <> "CellPhone"
AND Temp.[Phone#] = Sheet1.[Phone#]



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
And thinking about this a little more, you might not need the union query at all.

SELECT *
FROM Sheet1
WHERE PhoneType <> "CellPhone"
OR

(Not Exists
(SELECT *
FROM Sheet1 as Temp
WHERE PhoneType <> "CellPhone"
AND Temp.[Phone#] = Sheet1.[Phone#]
AND PhoneType = "CellPhone")


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John said:
One way would be to use a UNION Query

SELECT *
FROM Sheet1
WHERE PhoneType <> "CellPhone"
UNION
SELECT *
FROM Sheet1
WHERE Not Exists
(SELECT *
FROM Sheet1 as Temp
WHERE PhoneType <> "CellPhone"
AND Temp.[Phone#] = Sheet1.[Phone#]



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Using Access 2003,XP OS

I am trying to run a query from a table (See the data below). I want
to run a query that check the phone#, if the phone number is the same
then i want to eliminate the phoneType where is equal to "Cellphone".

I used the query here but not working due to multiple fields have the
same input.

SELECT distinct Sheet1.Phone#, Sheet1.Fname, Sheet1.LName,
Sheet1.Dept#, Sheet1.Address, Sheet1.PhoneType, Sheet1.ClassNumber
FROM Sheet1;
WHERE Sheet1.PhoneType = "Cellphone"

I searched the google and found some said that access does not support
distinct clause!

Please help!
Fname Lname Dept# Phone# Address PhoneType ClassNumber

Susan Linsey 890123 123-456-789 456 ABC Street Home
MTK123
Susan Linsey 567880 123-456-789 456 ABC Street
Cellphone MTK123
Dana Choi 232111 447-123-456 123 DEF Drive Home STkk1
Megan Winer 454646 447-123-457 678 AAA Ct Home JKTF199
Christina Pope 453455 447-123-458 890 North Road
Home LLL77
Mike Scott 565656 447-123-459 888 S Court Home MNS68
David Nicole 654656 677-333-456 1111 Harlen St
Cellphone PO90
Bob Smith 511345 888-900-123 1202 Can Do Home HBC56
Bob Smith 907896 888-900-123 1202 Can Do Cellphone
HBC56
Click to expand...
Click to expand...
 
Back
Top