search multiple columns in one table for specific criteria

[Tasso]

Hi,
I have a huge table that contains three columns with data but only one
column per row can contain the search criteria, example column A=Phone1,
B=Phone2 and C=Phone3. The search criteria is "Home Phone". I am trying to
search all three columns for the search criteria and biuld one column with
the results. Also when performing the search I am trying to return the column
adjacent to the maatching column that contains the actual phone number,
example column A1=PhoneNum1, B1=PhoneNum2 and C1=PhoneNum3. When matching
column A,B,C I want to return the phone number in A1,B1,C1, example if
A="Home Phone" then A1, if B="Home Phone" then B1, if C="Home Phone" then C1.
Any help would be greatly appreciated.

 

[Ken Snell \(MVP\)]

This query is made difficult because your data table structure is not
normalized. Normally, one would have a single table of phone numbers, with a
field to show the type of phone number. What you have is three fields
showing the three types, so a query cannot search all three easily.

To get around your current setup, you can use a UNION query to normalize the
data, then use that query as the source of data for the search query.

The UNION query (save it as a separate query -- let's name it
qryUnionPhones) would look something like this (note that you can create
this query only in SQL view -- open a new query in design view, close the
"add tables" window without selecting anything, click on Query View icon on
toolbar, and select SQL View, then paste this generic code into the window,
and change the generic table names to your real names -- TableName must be
replaced with the real table name):

SELECT T1.Phone1 AS PhoneType,
T1.PhoneNum1 AS PhoneNumber, "Phone1" AS FieldSource
FROM TableName AS T1
UNION ALL
SELECT T2.Phone2,
T2.PhoneNum2, "Phone2" AS FieldSource
FROM TableName AS T2
UNION ALL
SELECT T3.Phone3,
T3.PhoneNum3, "Phone3" AS FieldSource
FROM TableName AS T3;


Now your search query can use the above query:

SELECT * FROM qryUnionPhones
WHERE PhoneType = "Home Phone";

 

[John Spencer]

You have a bad table design. IF you can't fix it then you will need to
use some complex code to get the desired result.

You should have at table for Telephone numbers with three (or more) Fields.

PersonID - a reference to the primary key in your current table
PhoneNumber - the phone number
PhoneType - what you are currently storing in a, b, and c columns.

Again if you can not change your table design you will need code like
the following to return the first match (if any) on Home Phone.

IIF(A="Home Phone",A1, IIF(B="Home Phone",B1,IIF(C="Home Phone",C1,Null)))

If you could have two numbers of the same type, then the problem becomes
more complex, especially if you want separators between the numbers such
as ";"

IIF(A="Home Phone",A1,NULL) & " " & IIF(B="Home Phone",B1,NULL) & " " &
IIF(C="Home Phone",C1,Null)
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Tasso]

The union all worked perfectly (Thank You!) but now I am trying to create a
query that shows each phone type in a column and it's not working.
SELECT qryUnionPhones.ID, T1.PhoneTpe as Home, T1.PhoneNumber as HomeNum,
T2.PhoneType as Work, T2.PhoneNumber as WorkNum, T3.PhoneType as Cell,
T3.PhoneNumber as CellNum
FROM qryUnionPhones, qryUnionPhones T1, qryUnionPhones T2, qryUnionPhones T3
WHERE T1.PhoneType="Home Phone" or T2.PhoneType="Work Phone" or
T3.PhoneType="Cell Phone";

 

[Ken Snell \(MVP\)]

You need to use three separate queries based on the union query, where each
one is filtered for the desired phone type:

qryUNIONHome
-----------
SELECT qryUnionPhones.ID, qryUnionPhones.PhoneType AS Home,
qryUnionPhones.PhoneNumber AS HomeNum
FROM qryUnionPhones
WHERE qryUnionPhones.PhoneType = "Home Phone";


qryUNIONWork
-----------
SELECT qryUnionPhones.ID, qryUnionPhones.PhoneType AS Work,
qryUnionPhones.PhoneNumber AS WorkNum
FROM qryUnionPhones
WHERE qryUnionPhones.PhoneType = "Work Phone";


qryUNIONCell
-------------
SELECT qryUnionPhones.ID, qryUnionPhones.PhoneType AS Cell,
qryUnionPhones.PhoneNumber AS CellNum
FROM qryUnionPhones
WHERE qryUnionPhones.PhoneType = "Cell Phone";



Then you can use a query similar to the one you've tried:

SELECT qryUnionPhones.ID,
qryUNIONHome.Home,
qryUNIONHome.PhoneNumber AS HomePhoneNumber,
qryUNIONWork.Work,
qryUNIONWork.PhoneNumber AS WorkPhoneNumber,
qryUNIONCell.Cell,
qryUNIONCell.PhoneNumber AS CellPhoneNumber
FROM ((qryUnionPhones LEFT JOIN qryUNIONHome
ON qryUnionPhones.ID = qryUNIONHome.ID)
LEFT JOIN qryUNIONWork ON
qryUnionPhones.ID = qryUNIONWork.ID)
LEFT JOIN qryUNIONCell ON
qryUnionPhones.ID = qryUNIONCell.ID;

 

[Ken Snell \(MVP\)]

How you extract the last seven digits of the phone number depends upon how
you're storing the data in the field. Assuming that you store only the
digits and no hyphens, dots, parentheses, or spaces, then yes you would use
the Right(FieldName, 7) to extract the last seven digits.

For comparing the numbers for duplicates, provide a bit more detail about
how you want to use that comparison -- in a query, in a form, in a report;
and do you want to display just the duplicates, or eliminate the duplicates?

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

 

[Tasso]

I would use a query with the data set compiled so far and do a
If(homephonenum=cellphonenum,"DupCell",if(homephonenum=workphonenum,"DupWork",homephonenum)) for each phone type.

 

[Ken Snell \(MVP\)]

Ah, ok, then use a query like this (qryU is a generic name for the query
that we built in the last part of this thread):

SELECT qryU.ID,
qryU.HomePhoneNumber,
qryU.WorkPhoneNumber,
qryU.CellPhoneNumber,
(qryU.HomePhoneNumber = qryU.CellPhoneNumber)
AS CellSameAsHome,
(qryU.HomePhoneNumber = qryU.WorkPhoneNumber)
AS WorkSameAsHome
FROM qryU;


or

SELECT qryU.ID,
qryU.HomePhoneNumber,
qryU.WorkPhoneNumber,
qryU.CellPhoneNumber,
(qryU.HomePhoneNumber = qryU.CellPhoneNumber)
AS CellSameAsHome,
(qryU.HomePhoneNumber = qryU.WorkPhoneNumber)
AS WorkSameAsHome
FROM qryU
WHERE (qryU.HomePhoneNumber = qryU.CellPhoneNumber) = TRUE
OR (qryU.HomePhoneNumber = qryU.WorkPhoneNumber) = TRUE;



or

SELECT qryU.ID,
qryU.HomePhoneNumber,
qryU.WorkPhoneNumber,
qryU.CellPhoneNumber,
(IIf(qryU.HomePhoneNumber = qryU.CellPhoneNumber, "DupCell",Null))
AS CellSameAsHome,
(IIf(qryU.HomePhoneNumber = qryU.WorkPhoneNumber, "DupWork", Null))
AS WorkSameAsHome
FROM qryU;


or any variation of the above.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/