[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 355
  • Last Modified:

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
0
FOS-Bret
Asked:
FOS-Bret
  • 2
1 Solution
 
Patrick MatthewsCommented:
You'd be better off redesigning the table(s).  In any event, working with what you have...


SELECT [Name], HomePhone AS PhoneNo
FROM tblPhoneNumbers
WHERE RoboHome = True
UNION
SELECT [Name], WorkPhone AS PhoneNo
FROM tblPhoneNumbers
WHERE RoboWork = True
UNION
SELECT [Name], CellPhone AS PhoneNo
FROM tblPhoneNumbers
WHERE RoboCell = True
ORDER BY [Name]

Open in new window

0
 
David KrollCommented:
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'
0
 
Patrick MatthewsCommented:
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

0
 
FOS-BretAuthor Commented:
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!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now