I have the following union query that works great, but I want to alter
the query to only return True (-1) for the TEST column.
Currently the test column DOES return 0 or -1. I just want the -1.
How can I place logic in this without creating another query.
Here is the union:
SELECT AgencyCode as ContactTypeID, AgencyShortName as ContactName,
Right(RegionName,1) as Region, "Agency" as ContactType,
ChkAgyDIS("Agys",IIf([Forms]![frmMonthlyReports]![txtReportType]="Agys",[contactTypeID]))
as TEST FROM dbo_V_EP_AllAgenciesForAppActivity UNION ALL
SELECT DISTINCT DISCODE as ContactTypeID, LName & ", " & FName as
ContactName, Right(RegionName,1) as Region, "DIS" as ContactType,
ChkAgyDIS("Agys",IIf([Forms]![frmMonthlyReports]![txtReportType]="Agys",[contactTypeID]))
as TEST FROM qryDISContactsWithRegionInfo UNION ALL SELECT DISTINCT
EnterprisePartyRoleDescription as ContactTypeID, LName & ", " & FName
as ContactName, IIF(LNAME=Person 1,"1",IIF(Instr(LNAME,'Person
2')>0,"2",IIF(Instr(LNAME,'Person 3')>0,"3",IIF(Instr(LNAME,'Person
4')>0,"4","UNKNOWN")))) as Region, "Region" as ContactType,
ChkAgyDIS("Agys",IIf([Forms]![frmMonthlyReports]![txtReportType]="Agys",[contactTypeID]))
as TEST FROM dbo_V_EP_GAEmailContactsForAppActivity WHERE
EnterprisePartyRoleValue ='RSM';
TIA
-Brian 2 2247
BerkshireGuy wrote:
I have the following union query that works great, but I want to alter
the query to only return True (-1) for the TEST column.
Currently the test column DOES return 0 or -1. I just want the -1.
How can I place logic in this without creating another query.
Here is the union:
SELECT AgencyCode as ContactTypeID, AgencyShortName as ContactName,
Right(RegionName,1) as Region, "Agency" as ContactType,
ChkAgyDIS("Agys",IIf([Forms]![frmMonthlyReports]![txtReportType]="Agys",[contactTypeID]))
as TEST FROM dbo_V_EP_AllAgenciesForAppActivity UNION ALL
SELECT DISTINCT DISCODE as ContactTypeID, LName & ", " & FName as
ContactName, Right(RegionName,1) as Region, "DIS" as ContactType,
ChkAgyDIS("Agys",IIf([Forms]![frmMonthlyReports]![txtReportType]="Agys",[contactTypeID]))
as TEST FROM qryDISContactsWithRegionInfo UNION ALL SELECT DISTINCT
EnterprisePartyRoleDescription as ContactTypeID, LName & ", " & FName
as ContactName, IIF(LNAME=Person 1,"1",IIF(Instr(LNAME,'Person
2')>0,"2",IIF(Instr(LNAME,'Person 3')>0,"3",IIF(Instr(LNAME,'Person
4')>0,"4","UNKNOWN")))) as Region, "Region" as ContactType,
ChkAgyDIS("Agys",IIf([Forms]![frmMonthlyReports]![txtReportType]="Agys",[contactTypeID]))
as TEST FROM dbo_V_EP_GAEmailContactsForAppActivity WHERE
EnterprisePartyRoleValue ='RSM';
Where's the TEST=0 in your filter? If the value is called different
names in different tables, you have to alias them so they all have the
same name.
"BerkshireGuy" <bd*****@yahoo.comwrote in
news:11*********************@d34g2000cwd.googlegro ups.com:
I have the following union query that works great, but I want
to alter the query to only return True (-1) for the TEST
column.
Currently the test column DOES return 0 or -1. I just want
the -1.
How can I place logic in this without creating another query.
Firstly you need to properly format your query, it's a dog's
breakfast. Break after keywords, after each field definition and
elsewhere as required.
SELECT
AgencyCode as ContactTypeID,
AgencyShortName as ContactName,
Right(RegionName,1) as Region,
"Agency" as ContactType,
ChkAgyDIS("Agys",
IIf([Forms]![frmMonthlyReports]![txtReportType]="Agys",
[contactTypeID])) as TEST
FROM
dbo_V_EP_AllAgenciesForAppActivity
UNION ALL
SELECT DISTINCT
DISCODE as ContactTypeID,
LName & ", " & FName as ContactName,
Right(RegionName,1) as Region,
"DIS" as ContactType,
ChkAgyDIS("Agys",
IIf([Forms]![frmMonthlyReports]![txtReportType]="Agys",
[contactTypeID])) as TEST
FROM
qryDISContactsWithRegionInfo
UNION ALL
SELECT DISTINCT
EnterprisePartyRoleDescription as ContactTypeID,
LName & ", " & FName
as ContactName,
IIF(LNAME=Person 1,"1",
IIF(Instr(LNAME,'Person 2')>0,"2",
IIF(Instr(LNAME,'Person 3')>0,"3",
IIF(Instr(LNAME,'Person4')>0,"4",
"UNKNOWN")))) as Region,
"Region" as ContactType,
ChkAgyDIS("Agys",
IIf([Forms]![frmMonthlyReports]![txtReportType]="Agys",
[contactTypeID])) as TEST
FROM
dbo_V_EP_GAEmailContactsForAppActivity
WHERE
EnterprisePartyRoleValue ='RSM';
Then you need to insert a "WHERE clause individually in each of
the three queries, that limits the data to the true case of the
expression "test", after the "FROM" keyword and expression
FROM
dbo_V_EP_AllAgenciesForAppActivity..
WHERE
IIf([Forms]![frmMonthlyReports]![txtReportType]="Agys",
[contactTypeID])) = -1
is the first
the third already has a WHERE clause so you need to put an AND
to join the conditions
WHERE
EnterprisePartyRoleValue ='RSM';
AND
IIf([Forms]![frmMonthlyReports]![txtReportType]="Agys",
[contactTypeID])) = -1
Your homework is to do the second WHERE Clause..
You also are missing quotes around 'person 1' in
IIF(LNAME=Person 1,"1",
Bob Q
Here is the union:
SELECT AgencyCode as ContactTypeID, AgencyShortName as
ContactName, Right(RegionName,1) as Region, "Agency" as
ContactType,
ChkAgyDIS("Agys",IIf([Forms]![frmMonthlyReports]!
[txtReportType
]="Agys",[contactTypeID]))
as TEST FROM dbo_V_EP_AllAgenciesForAppActivity UNION ALL
SELECT DISTINCT DISCODE as ContactTypeID, LName & ", " & FName
as ContactName, Right(RegionName,1) as Region, "DIS" as
ContactType,
ChkAgyDIS("Agys",IIf([Forms]![frmMonthlyReports]!
[txtReportType
]="Agys",[contactTypeID]))
as TEST FROM qryDISContactsWithRegionInfo UNION ALL SELECT
DISTINCT
EnterprisePartyRoleDescription as ContactTypeID, LName & ", "
& FName as ContactName, IIF(LNAME=Person
1,"1",IIF(Instr(LNAME,'Person
2')>0,"2",IIF(Instr(LNAME,'Person
3')>0,"3",IIF(Instr(LNAME,'Person 4')>0,"4","UNKNOWN")))) as
Region, "Region" as ContactType,
ChkAgyDIS("Agys",IIf([Forms]![frmMonthlyReports]!
[txtReportType
]="Agys",[contactTypeID]))
as TEST FROM dbo_V_EP_GAEmailContactsForAppActivity WHERE
EnterprisePartyRoleValue ='RSM';
TIA
-Brian
--
Bob Quintal
PA is y I've altered my email address.
--
Posted via a free Usenet account from http://www.teranews.com This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: KENNY L. CHEN |
last post by:
Dear experts,
I have two tables in my Oracle 8i database: TEST (COL1,COl2,REC_NO)
and TEST1 (COL1,COL2,REC_NO).
Both tables are unique-indexed on (COL1,COL2,REC_NO).
I think the following...
|
by: Paradigm |
last post by:
I am using Access 2K as a front end to a MYSQL database.
I am trying to run a Union query on the MYSQL database. The query is (much
simplified)
SELECT as ID from faxdata UNION
SELECT as ID ...
|
by: Salad |
last post by:
A97.
Situation: I have 3 tables with a text field in each and a date field
in the first 2 tables:
Table1 Text1, Date1
Table2 Text2, Date2
Table3 Text3 (no date field)
The following...
|
by: mattytee123 |
last post by:
I have about 20 tables, of which I would like to do a union query and
count of how many of each different code there is?
The simplified verson of the table is structured like this.
Code ...
|
by: Jeremy |
last post by:
I have a situation where i am trying to run two query's and retrieve
one record from each query, then using Union combine them into one
recordset. The First Query is strait forward and should just...
|
by: BerkshireGuy |
last post by:
I have the following union query that runs great. I want to be able to
return the returns where the TEST field is True (-1) How do I add
criteria to do this without using another query?
SELECT...
|
by: janko.klemensek |
last post by:
Can I Group by the Union in one query or must I write two queries for
this (one for union and second for group by)?
|
by: wugon.net |
last post by:
question: db2 LUW V8 UNION ALL with table function month() have bad
query performance
Env:
db2 LUW V8 + FP14
Problem :
We have history data from 2005/01/01 ~ 2007/05/xx in single big...
|
by: MLH |
last post by:
I have a saved UNION query named
qryAuthsOwnersAddnlOwnersLienholders4OneCar.
Here it is...
SELECT & " " & & " " &
& " " & & ", " & & "
" & & " " & "(" & & " county) " &...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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...
|
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,...
|
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...
|
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...
| |