Add new values, but keep the old ones

[cos]

Right.. I have a table with all of my contracts, with main items being StartDate, EndDate and Price. All of those values are used in a query to show how much the client must pay (due to inflation, the prices vary every month, and the formula is fairly complex, one being:

March2012: IIf(#01/04/2012#>[StartDate] And [StartDate]>=#01/03/2012#,[Price],IIf([StartDate]>=#01/04/2012#,0,IIf([EndDate]<#01/03/2012#,0,[Feb2012]*[0312])))
Just to show you an example…

What I need is to be able to create a sub-contract (stored in another table, and linked via ContractID), where all of the details stay the same, except the variable outcomes the main ones being StartDate, EndDate and Price. So that in my query, the result will be shown yet, all of the old values will remain. For example:

The contract states that the EndDate is 01/03/2012, with the price of 1000. A sub-contract is made to change date to 01/05/2012 (an extension of two months), with the price changed to 1200.

What needs to happen in my query is that the prices up to 01/03/2012 to stay the same, with an additional 1200 shown in April, and 1200*Inflation in May.

Any ideas on how I may do this? It’s one of the worst parts of the system :banghead:

 

[G37Sam]

You're trying to accomplish so much at once that its overwhelmed you. Break it down, forget the whole inflation for now and assume a fixed rate for now.

Do you need store every months payment? Or will a total field with the sum of all payments do?

 

[cos]

i need to record all of the values for every month =/ and it's the worst thing, but the user requires to know the history of each contract..

 

[G37Sam]

So you'll need a SubcontractID table with the following structure:
SubcontractID, ContractID, StartDate, EndDate

Then you'll need a payments table that will be linked to your subcontract as follows:
PaymentID, SubContractID, PaymentMonth, PaymentAmount

Am I making sense?

 

[wmphoto]

I've a feeling this would be best solved by structuring your tables differently.

If you are using the contracts table for the main contract and then the subcontracts table for things such as extensions, and using a many to one relationship, then you have the parent and the child tables both being used to show how much the client has to pay. This makes it difficult when you start making queries because you cant decide which table to base it on.

What might work better, if the original contract and extensions need to remain separate is use the contract table just to store the basics and then all price information goes into subcontracts. So a new contract will have one subcontract containing the original start, end, and price (and inflation calculations as required) and you can create a field in subcontracts to identify this as original and others as an extensions. So then if the originak has an end date of 1/3/12 and you then extend it for 2 more months, you create a new subcontract with start date 2/3/12 and end date 1/5/12, and price as appropriate.

 

[cos]

those are both great ideas, however, when i add a new sub contract, the stupid things starts to duplicate contractID in the query which i use to create a form where the inflation is calculated and the whole thing is more visual, as you can see in my attached screen shot, ContractID 2 appears twise due to two sub contracts. Imagine how messy that form would look if there were 10 sub contracts.

is there any way of me preventing the duplication and so that i can create a more tidy view? just that i have no idea or what so ever..:banghead:




p.s. i hate my keyboard and this old pc, as i had to type this out 12 times, cuz the internet keeps failing..