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

Union Query Help - Return TRUE

P: n/a
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

Sep 28 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a

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.

Sep 28 '06 #2

P: n/a
"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

Sep 28 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.