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 2 3019
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
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
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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 |
|
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
|
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)
|
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...
|
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...
| |
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...
|
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'. ...
|
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,
|
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...
|
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...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
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...
|
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...
|
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
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
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...
| |