By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,665 Members | 1,430 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,665 IT Pros & Developers. It's quick & easy.

subquery, iif() problems - NEED HELP!

P: n/a
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
Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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.175164$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, 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

Nov 12 '05 #2

P: n/a
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.175164$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, 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

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.