Nubasian
asked on
Create a Calculation Formula in Access
I need help creating a calculation in Access. This calculation is the sum of money to be received if & when certain targets are met. The reason why I want to use Access is because that is where all the project data is stored. I was thinking I could create some kind of form, and plug in dates and yes, no answers, then get a result (sum).
For example
1.When a project starts, I get $75,000 (I'm thinking that a If - then expression would work for this but I'm not sure how to write it?)
2. When the project ends, I get $25,000 (Same thing here, I was thinking by just inputting a date in a field, then $25000 would get added to the sum)
3. Also, If the following three criteria is met, I get an additional $50,000
The 3 criteria are:
A. "Actual Project End Date" is not more than 30 days of "Project Promised End Date"
B. Final project costs are not over 105% of budget
C. Completed project is deliverd before "10/31/07" (This date will vary based on each project)
Can anyone tell me how I can create something in Access, using If-Then expressions, in a some kind of calculation form or query???? I need detailed instructions.
For example
1.When a project starts, I get $75,000 (I'm thinking that a If - then expression would work for this but I'm not sure how to write it?)
2. When the project ends, I get $25,000 (Same thing here, I was thinking by just inputting a date in a field, then $25000 would get added to the sum)
3. Also, If the following three criteria is met, I get an additional $50,000
The 3 criteria are:
A. "Actual Project End Date" is not more than 30 days of "Project Promised End Date"
B. Final project costs are not over 105% of budget
C. Completed project is deliverd before "10/31/07" (This date will vary based on each project)
Can anyone tell me how I can create something in Access, using If-Then expressions, in a some kind of calculation form or query???? I need detailed instructions.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Flyster & Ramfublio: I'm a access beginer user and don't really understand your instructions. I need further details on how to do what you're suggesting. Thanks! Nubasian.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Nubasian,
Laeijebor was gracious enough to provide the how to for my solution. (Thanks Laeijebor) If youre still not sure how to do it, then give me the name of your table and all the field names. Ill rewrite it and show you the easy way to make it work!
Flyster
Laeijebor was gracious enough to provide the how to for my solution. (Thanks Laeijebor) If youre still not sure how to do it, then give me the name of your table and all the field names. Ill rewrite it and show you the easy way to make it work!
Flyster
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.