in access, if then statement
in [Queries]
|
Prev: Function of colon between table names when query in design view
Next: Using a combo box to determine which field to search.
From: dtretina on 7 Jun 2010 15:11 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];
From: John Spencer on 7 Jun 2010 15:28 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
From: KARL DEWEY on 7 Jun 2010 16:13
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? -- Build a little, test a little. "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]; > |