Need to have Query Amend a Revised calculation

[sburgess]

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])

 

[neileg]

Design problem. I would be creating a new record each time and comparing its value with the previous record.

 

[sburgess]

I have a revisions subtable off of the main requisitions table, that would house all the revised info. There is also a subtable for the line items for the requsitions, which is has a one-to-many relationship from the line item table to the revision table.

Where would I want to put a new record?

 

[neileg]

Why would you want to store the value of the revision? Surely if you can calculate it you don't need to store it?

Give us a better idea of your design any we may be able to help.

 

[sburgess]

I don't necessarily need to store it, but I do need to be able to access the revision, and if there are additional revisions to the line item, be able to call out the previous revisions and update the new amount of the line item.

I have attached a copy of the my "work-in-progress" form.

The process is, we receive a requisition to issue a Purchase Order to a Vendor. This requisition might have a quote #, or it might not, so there is a Requisition ID that is a Unique Identfier. The Requisition table is the main table. Off of that table, is a Requisition Line Items Table, and Purchase Order Table. There is also a Revisions Table, and this is a subtable from the Purchase Order and Line Item Tables, amending the PO, and the line item, if there is an increase or a decrease in the Purchase Order. If there is a revised amount, this amount does need to replace the original line amount. And if there is a new revision to that, the new amount needs to replace the old amount, etc.

When Invoices are received from Vendors, the totals need to add up to the amounts that we have for each line item of our Purchase Order.