combine data

[jozino]

I have one table, PEOPLE, with names

id	nameLast	nameFirst
000003	Sherwood	Hugh
000004	Walter	Wolfgang - 01
000005	McFadden	Bruce - 01
000006	Power	Des

another table, TELE_LINK, with phone numbers and emails

idPerson	idTeleType	useAreaCodeFormat	numberOrAddress
000003	01	-1	2505463008
000003	03	-1	2505425276
000011	01	-1	2062921428
000011	02	-1	2062921426
000012	01	-1	6048829663
000012	02	-1	6048829669

idfTeleType represents what type it is - home phone, office phone, fax, email, etc.

I need to make a query which would show all phones and email in one row for one each person -

First Name

Last Name

Company

Phone

Home Phone

Other Phone

Mobile

Fax

Email

and put NULL or empty cell if record (tel. number or email) doesn't exist

I tried to do it for emails (TELE_LINK.idTeleType="10") only but is only shows records if email exists

SELECT PEOPLE.nameLast, PEOPLE.nameFirst, TELE_LINK.numberOrAddress
FROM TELE_LINK RIGHT JOIN PEOPLE ON TELE_LINK.idPerson = PEOPLE.id
WHERE (((TELE_LINK.idTeleType)="10"));

any idea please?

[ranman256]

You need a table to convert Telecode to Name.
01, home
02, cell
etc.

Qry1 will join your 3 tables to get all your data.
Q2 is a Crosstab qry of Q1 to put it in the format you have above.

[jozino]

Well, there is already this table

id	description	idTeleTypeType
01	Office Tel	1
02	Office Fax	2
03	Home Tel	1
04	Cell Phone	7
05	Toll Free	1
06	Internet	4
07	Office Tel2	1
08	Pager	3
09	Lake	1
10	E-mail	5
11	alternate fax	2
12	Fax	2
13	Tel	1
14	Cottage	1
15	web page	4
16	Summer	1
17	Winter	1
18	Tel2	1
19	Phone	1
20	Home Fax	2
21	Home Tel in summer	1
22	Home Tel in winter	1
23	apartment tel	1
24	510-271-2712	1

but I just want to attach home phone, office phone, fax and email to each person from the PEOPLE table (or empy cell or NULL if non existing)