Hello,
I am not sure how your tables are set up, so I will assume that everything is in one table, tblProjects.
First, you would add three fields: Bonus_Start, Bonus_End, Bonus_Incentive
I assume that you have the following two fields: TotalBudget, FinalCost, DeliveryTargetDate
With complicated tests, I like to write two queries: one with the tests, and one with the results.
First query (qryProjectsWithTests):
SELECT tblProjects.*,
[Actual Project End Date] IS NOT NULL AS IsCompleted,
[Actual Project End Date] <= [Project Promised End Date] + 30 AS IsOnTime,
[FinalCost] <= [TotalBudget] * 1.05 AS IsOnBudget,
[Actual Project End Date] <= [DeliveryTargetDate] AS MetTargetDate
FROM tblProjects;
Second query:
SELECT qryProjectsWithTests.* ,
Bonus_Start + Iif(IsCompleted, Bonus_End,0) +
Iif(IsOnTime AND IsOnBudget AND MetTargetDate, Bonus_Incentive,0) AS Bonus
FROM qryProjectsWithTests;
You would then make a form to allow the bonus amounts to be added to each project.
Main Topics
Browse All Topics
by: FlysterPosted on 2007-05-24 at 17:51:40ID: 19153924
Hi Nubasian,
This is what I came up with. It seems to work:
SELECT tblProject.ProjectID, tblProject.[Projected Start Date], tblProject.[Actual Start Date], tblProject.[Project Promised End Date], tblProject.[Actual Project End Date], tblProject.Budget, tblProject.[Final project costs], tblProject.[Project Delivered Date], IIf([Actual Start Date] Is Null,0,75000) AS [Start Fee], IIf([Actual Project End Date] Is Null,0,25000) AS [End Fee], IIf([Actual Project End Date]-[Project Promised End Date]<30,1,0) AS Criteria1, IIf([Final project costs]<=([Budget]*1.05),1,
FROM tblProject;
Flyster