Hi,

TblContactInfo is where I keep phone numbers of all types, e-mail addresses, and website addresses. I want to build a query that shows the person and every phone number (office, fax, cell, home, pager) I have for them, plus their e-mail and website if I have one. Right now I have accomplished this by building a separate query for each phone number, then bringing all of those queries together in one "umbrella" query. So for example, to show fax numbers, the query contains the ContactInfo field and a second field with criteria set to the ContactInfoType for Fax: 2

Contact Fax: ContactInfo
FKContactInfoType (Criteria: 2)

This is so cumbersome. I'm sure there's a way to do this without using a separate query for each phone number. How can I add a second, third, etc. field in the query to show the office #, pager #, cell phone # etc. in one query? Thanks.

Hello, sweeneyle. How are the different phone numbers distinguished in tblContactInfo? Can you throw out the list of fields in the table? Not quite clear on that and I'd like to help!

Kevin

Sure, thanks. TblContactInfo has:

PKContactInfoID (autonumber),
FKContactID (the person whose phone number we're referencing from TblContacts),
FKContactInfoType (the type of info - cell phone, e-mail, etc. from TblContactInfoType), and
ContactInfo (the phone number or whatever).

So the criteria - 2 - in the query is the ContactInfoType for Fax.

Hmmm, well the only thing I can think of is maybe using a crosstab query tying in tblContact (I assume that is the name since ContactId is a FK) and tblContactInfo. I haven't messed with crosstabs very much. Maybe you do something like this (and you'll have to test it):

TRANSFORM (tblContactInfo.ContactInfo) AS [The Value]
SELECT tblContact.ContactId AS ContactId
FROM tblContact INNER JOIN tblContactInfo ON tblContact.ContactId = tblContactInfo.ContactId
GROUP BY tblContact.ContactId
PIVOT tblContactInfo.ContactInfoType;

Just brainstorming with that. I took the one crosstab I have and changed it to the fields I think you'll need. Give that a shot. If anyone out there sees a glaring error with what I have, feel free to shoot it down. I don't want to lead sweeneyle down the wrong path!

Kevin