in access, if then statement

[dtretina]

if (Escalation/Assignment = 0) or (Updateheat = 0 )or (Worklog = 0), then 0
for the total. Else total.

How would you set this up in design query? Each of the above is a seperate
table

Query:
SELECT DISTINCTROW tblMonitoringData_OLD.[HEAT ID],
[tblEscalation/Assignment]![Points Earned]+tblUpdateHeat![Points
Earned]+tblWorklog![Points Earned] AS [Documenation Pts],
[tblEscalation/Assignment].[Points Earned] AS [Escalation?Assign],
tblUpdateHeat.[Points Earned] AS UpdateHeat, tblWorklog.[Points Earned] AS
Worklog
FROM ((tblMonitoringData_OLD LEFT JOIN [tblEscalation/Assignment] ON
tblMonitoringData_OLD.[Escalation/Assignment] =
[tblEscalation/Assignment].ID) LEFT JOIN tblUpdateHeat ON
tblMonitoringData_OLD.UpdateHeat = tblUpdateHeat.ID) LEFT JOIN tblWorklog ON
tblMonitoringData_OLD.Worklog = tblWorklog.ID
GROUP BY tblMonitoringData_OLD.[HEAT ID], [tblEscalation/Assignment].[Points
Earned], tblUpdateHeat.[Points Earned], tblWorklog.[Points Earned];

 

[John Spencer]

Do you mean each is a separate field in a separate table?

IIF(tblUpdateHeat.[Points Earned]=0 OR tblWorklog.[Points Earned] OR
[tblEscalation/Assignment]![Points Earned] = 0, 0,
[tblEscalation/Assignment]![Points Earned]+tblUpdateHeat![Points
Earned]+tblWorklog![Points Earned])


Basically the IIF consists of three arguments.
First a conditional statement X=Y
Second the response if the conditional statement is TRUE
Third the response if the conditional statement is NOT TRUE

SELECT DISTINCT tblMonitoringData_OLD.[HEAT ID],
IIF(tblUpdateHeat.[Points Earned]=0 OR tblWorklog.[Points Earned] OR
[tblEscalation/Assignment]![Points Earned] = 0, 0,
[tblEscalation/Assignment]![Points Earned]+tblUpdateHeat![Points
Earned]+tblWorklog![Points Earned]) AS [Documenation Pts]
, [tblEscalation/Assignment].[Points Earned] AS [Escalation?Assign]
, tblUpdateHeat.[Points Earned] AS UpdateHeat
, tblWorklog.[Points Earned] AS Worklog
FROM ((tblMonitoringData_OLD LEFT JOIN [tblEscalation/Assignment] ON
tblMonitoringData_OLD.[Escalation/Assignment] =
[tblEscalation/Assignment].ID)
LEFT JOIN tblUpdateHeat ON
tblMonitoringData_OLD.UpdateHeat = tblUpdateHeat.ID)
LEFT JOIN tblWorklog ON
tblMonitoringData_OLD.Worklog = tblWorklog.ID

Note that I have dropped the group by and changed DistinctRow to Distinct

if (Escalation/Assignment = 0) or (Updateheat = 0 )or (Worklog = 0), then 0
for the total. Else total.

How would you set this up in design query? Each of the above is a seperate
table

Query:
SELECT DISTINCTROW tblMonitoringData_OLD.[HEAT ID],
[tblEscalation/Assignment]![Points Earned]+tblUpdateHeat![Points
Earned]+tblWorklog![Points Earned] AS [Documenation Pts],
[tblEscalation/Assignment].[Points Earned] AS [Escalation?Assign],
tblUpdateHeat.[Points Earned] AS UpdateHeat, tblWorklog.[Points Earned] AS
Worklog
FROM ((tblMonitoringData_OLD LEFT JOIN [tblEscalation/Assignment] ON
tblMonitoringData_OLD.[Escalation/Assignment] =
[tblEscalation/Assignment].ID) LEFT JOIN tblUpdateHeat ON
tblMonitoringData_OLD.UpdateHeat = tblUpdateHeat.ID) LEFT JOIN tblWorklog ON
tblMonitoringData_OLD.Worklog = tblWorklog.ID
GROUP BY tblMonitoringData_OLD.[HEAT ID], [tblEscalation/Assignment].[Points
Earned], tblUpdateHeat.[Points Earned], tblWorklog.[Points Earned];

--

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

You have the same names as calculated fields and fields that you are joining
tables on.
Which do you want to use in the IIF stateement?