View Single Post
  #2  
Old June 7th, 2010, 08:28 PM posted to microsoft.public.access.queries
John Spencer[_2_]
external usenet poster
 
Posts: 4
Default in access, if then statement

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

dtretina wrote:
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