subquery, iif() problems - 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, PrimaryContactR eference 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].[PrimaryContactR eference]))

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

If have confirmed that the PrimaryContactR eference 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.Firs tName, tblContact.Last Name,
tblContact.Prim aryContactRefer ence,
IIf([tblContact].[PrimaryContact]=True,[tblContact].[City],(SELECT
[tblContact].[City] from tblContact WHERE
[tblContact].[ContactID]=PrimaryContact Reference)) 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

[Wayne Morgan]

Try a DLookup statement instead of the SELECT query in the False part of the
IIF statement.

--
Wayne Morgan
"edself" <ed****@ecomail .org> wrote in message
news:x1fsb.1751 64$Fm2.154196@a ttbi_s04...

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, PrimaryContactR eference 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].[PrimaryContactR eference]))

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

If have confirmed that the PrimaryContactR eference 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.Firs tName, tblContact.Last Name,
tblContact.Prim aryContactRefer ence,
IIf([tblContact].[PrimaryContact]=True,[tblContact].[City],(SELECT
[tblContact].[City] from tblContact WHERE
[tblContact].[ContactID]=PrimaryContact Reference)) 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

[Iago Gallego]

Ed,
I'd be tempted to do this with a union query. While they run a bit
slower than normal queries they can be much easier to construct.

The trick, for me, with union queries is to construct each part in its
own temporary query, check that the individual bits work and have the
same number of columns. Then cut and paste the SQL of each part into
the final query.

The result could look like
<SQL select query one>
UNION
<SQL Select query two>

If you do all the people for whom PrimaryContact is true as query 1
and all the others as query 2, it should be a lot easier to get the
two halves right than in one go.

Best of luck,
Martin

"edself" <ed****@ecomail .org> wrote in message news:<x1fsb.175 164$Fm2.154196@ attbi_s04>...

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, PrimaryContactR eference 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].[PrimaryContactR eference]))

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

If have confirmed that the PrimaryContactR eference 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.Firs tName, tblContact.Last Name,
tblContact.Prim aryContactRefer ence,
IIf([tblContact].[PrimaryContact]=True,[tblContact].[City],(SELECT
[tblContact].[City] from tblContact WHERE
[tblContact].[ContactID]=PrimaryContact Reference)) 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