473,725 Members | 1,782 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

subquery confusion - need help

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
Nov 12 '05 #1
2 3017

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].[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.
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.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?


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
Nov 12 '05 #2
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" <ed****@ecomail .org> wrote in message
news:__esb.1745 03$Tr4.470011@a ttbi_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, 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

Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2388
by: Vikram Vaswani | last post by:
Hi all, I have the following two tables: mysql> SELECT * FROM clients; +-----+-----------------------------+ | cid | cname | +-----+-----------------------------+ | 101 | JV Real Estate | | 102 | ABC Talent Agency |
2
2061
by: Nachi | last post by:
Hi, Urgent Help appreciated.... I am getting resultset with first condition and when try to get the resutlset from second condition i am getting the above error in SQL200. I know that i am getting more then one value in the second condition but even i am getting more then one value in my first statement
4
3826
by: Kenny G | last post by:
Below is a query that I currently have. I need to produce a subquery so that the top five of the CodeCount is returned. I appreciate your help. SELECT .PX_SURGEON, .PX_CODE, Count(.PX_CODE) AS CodeCount, .PX_TITLE FROM INNER JOIN ON .PX_CODE = .PX_CODE GROUP BY .PX_SURGEON, .PX_CODE, .PX_TITLE ORDER BY .PX_SURGEON, Count(.PX_CODE)
8
19595
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a couple of tables in my database using INNER JOINS and the WHERE clause to specify the required constraints. However, I also want to read two fields from a *single* record from a table called 'Locations' and then apply one of these field's values...
7
2369
by: K. Crothers | last post by:
I administer a mechanical engineering database. I need to build a query which uses the results from a subquery as its input or criterion. I am attempting to find all of the component parts of which a part may be composed. I have a table of parts and their subparts. The problem is that each of those subparts may be composed of smaller component parts. The subpart would then be listed in the Part field linked to each of its subparts in...
6
34920
by: phillip.s.powell | last post by:
update student s set school_year_id = (select distinct s.id from school_year s, interns i where lower(s.school_year_name) = lower(i.enrollment_year)and s.unique_key = i.unique_key group by s.id); ERROR 1242 (21000): Subquery returns more than 1 row I am trying to replace a column in interns.student.school_year_id to read an ID from the school_year table, where school_year_name will map to interns.interns.enrollment_year I can't for...
2
1481
by: dcourington | last post by:
I'm new to this group and not an experienced Access user, but have solved several of my problems already by reading other threads on this forum (Thanks!). Here's my current problem that I can't figure out how to solve: I'm trying to calculate total project management budget by project phase. I have a query that returns data grouped by project and index code, and has the sum of the PM budgets along with a field called 'WBS_CONCAT_Name'. ...
0
2065
by: kiran2nirvan | last post by:
hi please help in solving this i am recieving this error"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." for the code CREATE procedure . (@frmMasterID int,
1
4166
by: jcf378 | last post by:
Hi all-- Does anyone have any insight as to how I might create a search form that allows a user to select criteria based on any related table in the whole database. The search form I have now only allows me to filter based on variables in a single table. I would like to have a search form where I can select multiple variables (from various linked tables) to filter by, and return results based on this multi-table filter. Allen Browne...
0
8872
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9246
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9162
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8069
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6694
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4505
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3211
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2619
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
2150
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.