I have a query which calculates and original amount, looks to see if there is an increase or a decrease in the amount, and if it's an increase, it adds the original amount to the amount of the increase, and gives a "revised" amount. If it's a decrease, it subracts the original amount from the amount of the decrease, and gives a "revised" amount as well. This works fine for the line item, as long as there's only one Revision to the line item.

What I need it to do, and I'm not sure if I should still be doing this at a query stage, or if it should be coded, is, if there is more than one revision to the line item, it needs to look at the new "revised" amount (from the first revision), and then add the increase or subtract the decrease from the revised amount, and give a new "revised" amount, instead of taking it from the original amount.

example of what it's doing now.

Rev #1 Orginal line item - $4,300 Increase - $500 New Revised amount - $4,800
Rev #2 Original line item - $4,300 Increase - $50 New Revised amount - $4,350

What it should be doing.

Rev #1 Orginal line item - $4,300 Increase - $500 New Revised amount - $4,800
Rev #2 Revised Line item - $4,800 Increase - $50 New Revised amount - $4,850

I need to have it look to see if the revision # is higher than 1, and then look at the revised amount, and do the increase or decrease at that time, and then give another "revised" amount.

Is this doable? And if so, any help would be greatly appreciated!


Here is the Expression that I have in my query to calculate my "revised" amount. It looks at the original amount, and looks to see if it's an increase, and if it is, it adds it to the revision amount, and gives a new total for the line item, otherwise it sees that it's a decrease, and it subtracts the revision amount from the original, and gives an new total.

New Amount: IIf(tblRevisions![Increase?]=True,[revision Amount]+[Line Item Amount],[Line Item Amount]-[Revision Amount])

sburgess,

First you check what Revision is currently going on then calculate the figure.

It require more IIF statements as there are multiple conditions to check. You may please play with following IIF statement I created long back to get the correct value.(In your case calculations)

The inner IIF will be executed first and then outer and finally the most outer.


 =IIf([D1]="",(IIf([d2]="",(IIf([d3]="",[d4],[d3])),[d2])),[d1])

It confuses a little hence I colored to the inner condition that will be logically executed first.

Instead of my variable d1, d2.....you may please change it with your calculation. This loop is for up to 4 revisions to check. If need more Revisions to check, you might need to increase most outer IIF statements but that will be little more complicated I believe.

I hope this will give you an idea at least.


With kind regards,
Ashfaque

Are there any other fields in this table that make the record unique? For example, does hte revision belong to a client so that you can look up all instances of that client and do this arithmetic, or client and date? If so, we can also code this function as well.

Are you storing the resulting value in a table, or is it just showing up on a report?

Also, why aren't you entering the values as positive and negative and then summing them?

HTH


mmcdonal

I am going to change the values to postive and negatives. When I originally set it up, I thought it would be easier to look for increases or decreases.

I am not storing the resulting value, although I probably need to, since the new amount will replace the original amount. I have been told that I shouldn't be storing a calculation. I do need to. If I did that, then I could reference that amount. Can you give me a little information on how to store the calculation, since that is a new one for me?

Ashfaque - I will try your IIF statement and see how it works.

Thanks for all the help. Seems I get one thing to work, and then I get stuck at another spot. These are crucial for this database though.

The Requisition # is unique, and so is the Purchase Order #.

I have tried using a DSUM statement, but I'm not pulling the correct information.

RunningSum: DSum([Revision Amount],"tblRevisions"," [Line Item #] = " & [Line Item #] & " And [Purchase Order]<=" & [Purchase Order])

I want it to create a running sum, if the Purchase Order is = to Purchase Order and the Line Item # is = Line Item #, then I want it to add the Revision Amounts, and put the total in the Running Sum field.

The issue is, the Purchase Order might have multiple line items, but not all line items will be revised (increased/decreased). So, it needs to look at the Purchase Order and the Line Item # to pull the correct information.

Any help would be great!