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, 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

[Peter Miller]

Re: subquery confusion - need help


On Wed, 12 Nov 2003 00:18:02 GMT, "edself" <edself@ecomail .org> wrote
in comp.databases. ms-access:
[color=blue]
>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.[/color]

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.
[color=blue]
>Here is the entire query, including the iif(... subquery...)
>
>SELECT tblContact.Firs tName, tblContact.Last Name,
>tblContact.Pri maryContactRefe rence,
>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?[/color]

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(PrimaryCont act,c.City,p.Ci ty)
FROM tblContact as c left join tblContact as p
on c.primarycontac treference=p.co ntactid;

Another possibility is to set the primarycontactr eference 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=prima rycontactrefere nce?).
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.primarycontac treference=p.co ntactid;

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]

Re: subquery confusion - need help

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.Contac tID, Contacts.Name, Contacts_1.Addr ess
FROM Contacts INNER JOIN Contacts AS Contacts_1 ON
Contacts.Primar yContactReferen ce = Contacts_1.Cont actID
WHERE (((Contacts.Pri maryContact)=Fa lse));

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" <edself@ecomail .org> wrote in message
news:__esb.1745 03$Tr4.470011@a ttbi_s03...[color=blue]
> 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[/color]
contains[color=blue]
> 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[/color]
refers[color=blue]
> 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[/color]
follows:[color=blue]
>
> 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
>
>[/color]