subquery confusion - need help

[edself]

Greetings,

I am semi-new to Access and have a query question. I presume the solution
is easy, but need some help.

I have created a database with a Contact table. The contact table contains
address fields among other things. Because some contacts share the same
address, I included a boolean field, PrimaryContact. If true, a given
contact's record contains the address info for that contact. If
PrimaryContact is false, then another field, PrimaryContactReference refers
to the ContactID of a different record which contains the address info for
the contact. Make sense so far?

I have been trying to create a query which will seamlessly provide address
info for all contacts whether they are primary contacts or not.

As an example, one field in the query retrieves the city for all contacts.
I tried to use the iif() function to retrieve the necessary info as follows:

City: IIf([tblContact].[PrimaryContact]=True,[tblContact].[City],(SELECT
[tblContact].[City] from tblContact WHERE
[tblContact].[ContactID]=[tblContact].[PrimaryContactReference]))

This works if PrimaryContact=True, but returns nothing (i.e. a blank) if
PrimaryContact=False

If have confirmed that the PrimaryContactReference is accurate, actually
referring to a valid contact, so am not sure why this is not working.

Here is the entire query, including the iif(... subquery...)

SELECT tblContact.FirstName, tblContact.LastName,
tblContact.PrimaryContactReference,
IIf([tblContact].[PrimaryContact]=True,[tblContact].[City],(SELECT
[tblContact].[City] from tblContact WHERE
[tblContact].[ContactID]=PrimaryContactReference)) AS City
FROM tblContact;

Am I on the right track here, or is there a totally different way to do
this?

Thanks in advance for your help.

Ed

[Peter Miller]

On Wed, 12 Nov 2003 00:18:02 GMT, "edself" <ed****@ecomail.org> wrote
in comp.databases.ms-access:

City: IIf([tblContact].[PrimaryContact]=True,[tblContact].[City],(SELECT
[tblContact].[City] from tblContact WHERE
[tblContact].[ContactID]=[tblContact].[PrimaryContactReference]))

This works if PrimaryContact=True, but returns nothing (i.e. a blank) if
PrimaryContact=False

If have confirmed that the PrimaryContactReference is accurate, actually
referring to a valid contact, so am not sure why this is not working.
Well, because iif is working as expected (ie, it checks the condition,
and if true, provides the local city field), but subquery's aren't
simply queries embedded anywhere within other queries. More
specifically, ii is a vba construct, and the select statement you are
embedding for use when the flag is false is a sql statement. Iif
can't execute the select statement when the condition is false, which
is why you only get a meaningful result when the flag is true.
Here is the entire query, including the iif(... subquery...)

SELECT tblContact.FirstName, tblContact.LastName,
tblContact.PrimaryContactReference,
IIf([tblContact].[PrimaryContact]=True,[tblContact].[City],(SELECT
[tblContact].[City] from tblContact WHERE
[tblContact].[ContactID]=PrimaryContactReference)) AS City
FROM tblContact;

Am I on the right track here, or is there a totally different way to do
this?

Well, you're sort of on track, but there are several 'right' ways to
do it, and using 'iif' isn't one of them. Here's a workable solution.

SELECT c.FirstName, c.LastName, IIf(PrimaryContact,c.City,p.City)
FROM tblContact as c left join tblContact as p
on c.primarycontactreference=p.contactid;

Another possibility is to set the primarycontactreference field to
equal contactid on records where primarycontact=true, then simply
eliminate the primarycontact boolean field (since that information is
already contained in the test contactid=primarycontactreference?).
The advantage of doing this is that your query would then simply
become:

SELECT c.FirstName, c.LastName, p.City
FROM tblContact as c left join tblContact as p
on c.primarycontactreference=p.contactid;

There's other (better) ways to do this, but that should get you
started.

Peter Miller
__________________________________________________ __________
PK Solutions -- Data Recovery for Microsoft Access/Jet/SQL
Free quotes, Guaranteed lowest prices and best results
www.pksolutions.com 1.866.FILE.FIX 1.760.476.9051

[bzamfir]

Hi,

You should use a union query.

It will looks like this:

SELECT [Contacts].[ContactID], [Contacts].[Name], [Contacts].[Address]
FROM Contacts
WHERE ((([Contacts].[PrimaryContact])=True))
union
SELECT Contacts.ContactID, Contacts.Name, Contacts_1.Address
FROM Contacts INNER JOIN Contacts AS Contacts_1 ON
Contacts.PrimaryContactReference = Contacts_1.ContactID
WHERE (((Contacts.PrimaryContact)=False));

I selected only a generic, Address field, but obviously you need to select
all fields who makes up your address

The important thing is you need to both Select's to have the same fields, in
the same order.

If you need any further assistance, you can contact me.

HTH,
Bogdan

____________________________
Independent consultant
"edself" <ed****@ecomail.org> wrote in message
news:__esb.174503$Tr4.470011@attbi_s03...

Greetings,

I am semi-new to Access and have a query question. I presume the solution
is easy, but need some help.

I have created a database with a Contact table. The contact table contains address fields among other things. Because some contacts share the same
address, I included a boolean field, PrimaryContact. If true, a given
contact's record contains the address info for that contact. If
PrimaryContact is false, then another field, PrimaryContactReference refers to the ContactID of a different record which contains the address info for
the contact. Make sense so far?

I have been trying to create a query which will seamlessly provide address
info for all contacts whether they are primary contacts or not.

As an example, one field in the query retrieves the city for all contacts.
I tried to use the iif() function to retrieve the necessary info as follows:
City: IIf([tblContact].[PrimaryContact]=True,[tblContact].[City],(SELECT
[tblContact].[City] from tblContact WHERE
[tblContact].[ContactID]=[tblContact].[PrimaryContactReference]))

This works if PrimaryContact=True, but returns nothing (i.e. a blank) if
PrimaryContact=False

If have confirmed that the PrimaryContactReference is accurate, actually
referring to a valid contact, so am not sure why this is not working.

Here is the entire query, including the iif(... subquery...)

SELECT tblContact.FirstName, tblContact.LastName,
tblContact.PrimaryContactReference,
IIf([tblContact].[PrimaryContact]=True,[tblContact].[City],(SELECT
[tblContact].[City] from tblContact WHERE
[tblContact].[ContactID]=PrimaryContactReference)) AS City
FROM tblContact;

Am I on the right track here, or is there a totally different way to do
this?

Thanks in advance for your help.

Ed