Summing up in report (1 Viewer)

[Anakardian]

I have been working for a while on getting a rather complicated calculation working right but after getting it to work I have been hitting the wall with a head that feels like a pinball ball.

The following calculation works for calculating the amount for each line for [IncentiveAmount] in the report:

=IIf([MarpolViolationCheck]="No" ,IIf([DetainedCheck]="No" ,IIf([FocusVesselCheck]="Yes" ,IIf([VettingPerformance]=1 ,FormatCurrency(DLookUp("[InitialIncentiveAmount]" ,"tblParameters","[ParametersID]=1") ,2)*IIf([TradeableVesselFactorMet]="Yes" ,1 ,0,7)*[Multiplier] ,(FormatCurrency(DLookUp("[InitialIncentiveAmount]","tblParameters","[ParametersID]=1") ,2)*([ActualTargetsMet]/5)*0,7)+IIf([TradeableVesselFactorMet]="Yes" ,0,3 ,0)*FormatCurrency(DLookUp("[InitialIncentiveAmount]","tblParameters","[ParametersID]=1") ,2)),(FormatCurrency(DLookUp("[InitialIncentiveAmount]","tblParameters","[ParametersID]=1") ,2)*([ActualTargetsMet]/5)*0,7)+IIf([TradeableVesselFactorMet]="Yes" ,0,3 ,0)*FormatCurrency(DLookUp("[InitialIncentiveAmount]","tblParameters","[ParametersID]=1") ,2)),FormatCurrency(0 ,2)),FormatCurrency(0 ,2))*[Days]/365

When I try to sum it up, it returns an #Error when using

=sum([IncentiveAmount])

The alternative I have found is to wrap the first code section with =sum() but this gives the same result.

Can someone help me find the door in this wall?

 

[pr2-eugin]

That is a lot of IIF's there.. Wow.. Ans also too many DLookUp's.. Could it not be a bit simpler?

 

[Anakardian]

Possibly, but I can't figure out how to make the iif's simpler.
It bassically is if you fulfill, this, this, this and this, then that, if one is not fulfilled, then this other one.
Do you have a suggestion on how to do it?

I could possibly get rid of the DLookUp but I am not sure how to do it.

 

[pr2-eugin]

I am not sure how your table structure is formatted.. but by the looks of it.. it can be replaced with a JOIN.. but again, I am not sure.. as you seem to hardcode the ParameterID = 1..

So if you could,
* Give a sample data - how it is in the table,
* With the process without using any IIF (just in plain English) - What you want to do i.e. the conditions to check, what you wish to see in Output...

It could help us understand better..

 

[Anakardian]

Let me start from the bottom.

The IIF group is shown in the attachment as a flowchart. I think that is the best way to visualise what it does.

The data comes as a query with the following fields:
Employee_No (Number)
PersonName (Text)
Rank (Text)
VesselName (Text)
StartPeriod (Date)
EndPeriod (Date)
VettingPerformanceResult (Number formatted as %)
MainEnginePerformanceResult (Number formatted as %)
BudgetPerformanceResult (Number formatted as %)
PSCPerformanceResult (Number formatted as %)
RiskToolPerformanceResult (Number formatted as %)
FocusVesselCheck (Text (Yes/No))
MarpolViolationCheck (Text (Yes/No))
DetainedCheck (Text (Yes/No))
TradeableVesselFactorMet (Text (Yes/No))
Days (Number)
Multiplier (Number)
ActualTargetsMet (Number)

This information is only for a person selected earlier in the process.

The query providing this information looks like this:

SELECT tblTempCrewPerformance.Employee_No, tblTempCrewPerformance.PersonName, tblTempCrewPerformance.Rank, tblTempCrewPerformance.VesselName, tblTempCrewPerformance.StartPeriod, tblTempCrewPerformance.EndPeriod, tblTempCrewPerformance.VettingPerformance, tblTempCrewPerformance.MainEnginePerformance, tblTempCrewPerformance.BudgetPerformance, tblTempCrewPerformance.PSCPerformance, tblTempCrewPerformance.RiskToolPerformance, tblTempCrewPerformance.FocusVesselCheck, tblTempCrewPerformance.MarpolViolationCheck, tblTempCrewPerformance.TradeableVesselFactorMet, tblTempCrewPerformance.Days, (SELECT FocusVesselMultiplier
FROM tblFocusVesselMultiplier
WHERE TargetsMet = ActualTargetsMet) AS Multiplier, tblTempCrewPerformance.ActualTargetsMet, tblTempCrewPerformance.DetainedCheck
FROM tblTempCrewPerformance
ORDER BY tblTempCrewPerformance.StartPeriod;

As the selected person is only on board a vessel for a certain period, the result returns a line for each period on board.

You got me thinking a bit and it might be possible to get rid rid of a couple of the IIF's if it is possible to use an and in the IIF. I have never been able to get it to work so therefore I took the long way.

 

[Anakardian]

Solved the problem like this:
Stored the intermediate results in a table that will only contain the data I am woking with.
Made a couple of update queries to add the required parameters to each record in order for the main calculation to take place.
Used an update query to calculate the amount and stored that.

The report now uses the table as its source and the summing up works using =sum([MyField])

Incredible how you can get set on getting a particular solution to work without considering the alternatives.