Query won't work with THIS table

  • Thread starter Thread starter Alain
  • Start date Start date
A

Alain

Hi,

Access 2002, Win2K Pro, French

I have a IdTelephone table with following fields:
Id: Autonumber, unike Key, unused here
idMembre: Member Id, long integer
IdType: type of phone number, integer: 1 = home phone, 2 = Work phone
Telephone: phone number, text
....

I want to merge each member's data in a query where home phone number
will be identified as TelephoneDom and work phone number will be named
TelephoneTr. Each person has either Home OR Work OR both.

Here is my inner join query:

SELECT Distinct Princ.IdMembre,

(Select Dom.Telephone FROM IdTelephone AS Dom WHERE (Princ.IdMembre =
Dom.IdMembre) AND (Dom.IdType = 1)) as TelephoneDom,

(Select Tr.Telephone FROM IdTelephone AS Tr WHERE (Princ.IdMembre =
Tr.Idmembre ) AND (Tr.IdType = 2)) as TelephoneTr

FROM IdTelephone AS Princ;

Same query generates correct data for similar tables (same fields, same
type of data, same indexes) about Mail addresses and eMail addresses.

On the Telephone table, this query generates:

* a «this subquery can return at most one record» message
OR
*correct result is displayed for a short time
above message is displayed
and all fields in query are then filled with #Name? Help says its an
#3354 error.

Why? How can I avoid this problem?

Thanks

Alain
 
Hi Alain,

It sounds like
at least one idMembre
has more than one record
with IdType = 1
or more than one record
with IdType = 2.

You could put TOP 1 in your
subqueries if you always wanted
to see just one number for each type

SELECT Distinct Princ.IdMembre,

(Select TOP 1 Dom.Telephone FROM IdTelephone AS Dom
WHERE (Princ.IdMembre = Dom.IdMembre)
AND (Dom.IdType = 1)) as TelephoneDom,

(Select TOP 1 Tr.Telephone FROM IdTelephone AS Tr
WHERE (Princ.IdMembre = Tr.Idmembre )
AND (Tr.IdType = 2)) as TelephoneTr

FROM IdTelephone AS Princ;

Or go back and fix records that have
more one record for an IdType.

to see those quickly (but cannot edit):

SELECT
IdMembre,
IdType
FROM
IdTelephone
GROUP BY
IdMembre,
IdType
Having Count(*) > 1;

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter
 
Back
Top