Creating formulas

[DRMOB]

I have a database that is somewhat flat. In short it consist of a Table1
which contains agency information and a Grant#. Table2 contains the items the
agency is requesting to purchase and the Grant#. Once these items are
approved, the qty approved and cost approved are entered. A letter gets
mailed to agency. If the agency comes back and needs to revise the qty
and/or cost of one or more items, the Revised numbers are entered into
Revised Qty and Cost fields. (Not disturbing what was originally approved.)
My problem is in getting the Total Revised Cost formula to be accurate.
Sorry this is lenghthy, but I am trying to give as much info as one might
need. A sample of what the data might look like is below:

Original Request
Item 1 – Training RequestedQty = 1 ReqCost = 1000
ApprovedQty = 1 ApprvdCost = 1000
RevisedQty = 0 RevisedCost = 0

Item 2 – Books RequestedQty = 2 ReqCost = 50
ApprovedQty = 2 ApprvdCost = 50 (per item)
RevisedQty = 0 RevisedCost = 0

Total Cost Approved all items = $1100

Same Request but Item 1 was revised:

Item 1 – Training RequestedQty = 1 ReqCost = 1000
ApprovedQty = 1 ApprvdCost = 1000
RevisedQty = 2 RevisedCost = 600 (per item)

Item 2 – Books RequestedQty = 2 ReqCost = 50
ApprovedQty = 2 ApprvdCost= 50
RevisedQty = 0 RevisedCost = 0

Revised Total Cost all items = $1300

Following is the sql code I have to populate the total fields on the forms:

SELECT ItemsReqTbl.GrantNumber, ItemsReqTbl.ItemID,
Sum([QtyReq]*[CostPerUnit]) AS TotalCost, Sum([QtyApproved]*[CostApproved])
AS [Total Approved], IIf([Total Approved]>1000,[Total Approved]*0.9) AS [Less
10%], IIf([Total Approved]>1000,[Total Approved]*0.9,[Total Approved]) AS
[Total Granted],FROM ItemsReqTbl
GROUP BY ItemsReqTbl.GrantNumber, ItemsReqTbl.ItemID;

On another note, I have a check box for RevisedApplication which is in
Table1. I have a form with 2 subforms. On Form1 if the RevisedApp box is not
checked I don’t want to see Revised Qty and Rev Cost fields on Form2; If it
is checked I do. I can’t seem to get this code either. Any help is greatly
appreciated. Thank in advance.

 

[Michel Walsh]

The following should compute the ApprovedQty*ApprvdCost if the
checkBoxControl is not checked, else, the RevisedQty*RevisedCost. Note that
in the second case, it takes the Approved values if the revisde quantity is
0, for any given item.

SUM( iif( (RevisedQty =0) OR ( NOT
FORMS!form2NameHere!checkBoxControlNameHere) ,
ApprovedQty*ApprvdCost, RevisedQty *
RevisedCost ) )



Vanderghast, Access MVP