Union Query Help - Return TRUE

[BerkshireGuy]

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

[pietlinden]

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.

[Bob Quintal]

"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