If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
in access, if then statement
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]; |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
in access, if then statement
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]; |
Thread Tools | |
Display Modes | |
|
|