Phone Record Extraction Difficulties

[databasedonr]

Greetings all:

I have a challenge for extracting phone numbers.

I have a database with mulitple tables, one of which is tblcontact and another a tblphone.

In my tblcontact I have fields like: contactid, lastname, firstname etc.

In tblphone I have phoneid, contactid, phonetype and phonenumber.

In the phonetype field are text strings like: "Business", "Pager", "Email" and "Cell"; the phonenumber field has the appropriate data for the type. This means I have up to 4 rows in my tblphone for a contact.

When I retrieve these records, I want to display them in both form and a report as four (or less) fields on one row.


For example, if my contactid is 12345, I have rows for each type of phone in the phone table (up to four). I want this to display as:

Contact Business Email Cell
databasedonr 123-4567 donr@access.ca 132-6547

Please help! I'm really at a loss for how to do this: I have tried creating expressions based on the phonetype, but can only get it to work for one phonetype.

I have also tried writing a subquery as follows, and this tells me that I can only return one row of data.

Some of the fabulous expertise from this forum, I hope, will help me solve this problem.

Here's the subquery I tried:

SELECT [tblcontact].[lastname], [tblcontact].[firstname], (SELECT[phonenumber]FROM [tblphone]where[phonetype]="Email") AS Email
FROM tblPhone INNER JOIN tblContact ON [tblPhone].[ContactID]=[tblContact].[ContactID]
WHERE ((([tblcontact].[lastname])="[User Input]") And (([tblcontact].[firstname])="[User Input]"))
ORDER BY [tblcontact].[lastname], [tblcontact].[firstname];

Any suggestions hugely appreciated:

Thanks in advance.

 

[Jon K]

SELECT tblContact.lastname, tblContact.firstname,
(Select phonenumber from tblPhone where ContactID=tblContact.ContactID and PhoneType="Business") AS Business,
(Select phonenumber from tblPhone where ContactID=tblContact.ContactID and PhoneType="Pager") AS Pager,
(Select phonenumber from tblPhone where ContactID=tblContact.ContactID and PhoneType="Email") AS Email,
(Select phonenumber from tblPhone where ContactID=tblContact.ContactID and PhoneType="Cell") AS Cell
FROM tblContact
WHERE tblcontact.lastname=[Enter Last Name] And tblcontact.firstname=[Enter First Name]
ORDER BY tblcontact.lastname, tblcontact.firstname;

 

[databasedonr]

Thanks you, thank you, a million times.

Exactly what I was trying to do. I am very impressed - this works exactly right on the form -- for a single user.

If I want to use similar logic for a phone book report, however, I run into problems.

Is there a solution that will allow me to run a report for a group of users to extract their phone numbers?

 

[databasedonr]

But For Mulitple Rows?

This query worked exactly right for a single instance (i.e. one contact record) -- but if I want to produce a phone book, I am summarily informed that "At most one record can be returned by this subquery."

Keeping in mind the table structure I have, any ideas how I might produce a phone report for multiple users?

Thanks, humbly, in advance.

 

[databasedonr]

Found the solution

In my quest to make a phone book out of these records, I found a solution that works quite nicely, and I wanted to share it in the event others were trying to do something similar.

I recreated the query as:

select c.contactid, c.lastname
,max(iif(p.phonetype="Business",p.phonenumber,"")) as Business
,max(iif(p.phonetype="Pager",p.phonenumber,"")) as Pager
,max(iif(p.phonetype="Email",p.phonenumber,"")) as Email
,max(iif(p.phonetype="Cell",p.phonenumber,"")) as Cell
from tblPhone p,tblcontact c
where p.contactid = c.contactid
group by c.contactid, c.lastname

and this allows me to add criteria to the lastname field (and to add other fields in here - like dept, etc.) so I can sort and output to a report.

Good luck to anyone else who is trying to do manage their phone numbers like this -- following the rules of normalisation led me down an interesting path!

Thanks to all who read this, and pondered a solution, thanks again to Jon for getting me started down the right path.