asked on

Access / SQL - Multiple hits from single record.

Hi All,

I have an Access database with the following fields:

Name
HomePhone
CellPhone
WorkPhone
RoboHome
RoboWork
RoboCell

The "robo" fields indicate that a phone number should be placed on the RoboCall list. So far, so good. Except, I'd like to get each phone number on the list as a separate line of my query.

So, if I have these records...

Name	HomePhone	CellPhone	WorkPhone	RoboHome	RoboCell	RoboWork
====	=========	=========	=========	========	========	========
Bret	816-555-0000	816-555-1111	816-555-2222	FALSE		TRUE		FALSE
Fred	913-555-9999	913-555-8888	913-555-7777	TRUE		FALSE		FALSE
Art	417-555-3333	417-555-4444	417-555-5555	TRUE		TRUE		TRUE

Open in new window

I'd like to see these results...

Bret	816-555-1111
Fred	913-555-9999
Art	417-555-3333
Art	417-555-4444
Art	417-555-5555

Open in new window

In effect, I'd like to separate multiple hits from the same record. I have no idea how to do this with an Access Query (or with SQL outside of Access). Any help will be much appreciated.

Thanks,
Bret

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David Kroll

try something like this:

select name, homephone as phone
from table
where robohome = 'TRUE'
union
select name, cellphone as phone
from table
where robocell = 'TRUE'
union
select name, workphone as phone
from table
where robowork = 'TRUE'

Patrick Matthews

The redesign I have in mind:

tblPeople
-------------------------------------
PersonID (PK)
FName
LName

tblPhoneNumberType
-------------------------------------
PhoneNumberTypeID (PK)
PhoneNumberTypeName (e.g., home, work, cell)

tblPhoneNumbers
-------------------------------------
PersonID (PK, FK)
PhoneNumberTypeID (PK, FK)
PhoneNumber
UseForRoboCall

Your query then becomes:

SELECT p.LName, p.FName, t.PhoneNumberTypeName, n.PhoneNumber
FROM tblPeople p INNER JOIN
    tblPhoneNumbers n ON p.PersonID =  n.PersonID INNER JOIN
    tblPhoneNumberType t ON n.PhoneNumberTypeID = t.PhoneNumberTypeID
WHERE n.UseForRoboCall = True
ORDER BY p.LName, p.FName, t.PhoneNumberTypeName

Open in new window

FOS-Bret

ASKER

I can't change the design of the table as it's actually got a lot more fields than that and it's already in use. Your first suggestion worked like a charm, though. Thanks much!