How can I structure my tables to track old rates? 2

[VBAjedi]

I don't get over into this neck of the woods very often (spend most of my time in the VBA forum), so thanks in advance for your input. . .

I'm building a database to track contracts we sell. Included in my list of tables I have a ContractLineItems table, a Products table, a Regions table and a Rates/Costs table. The ContractLineItem specifies the Product and the Region, and then a query returns the Rate and our internal Costs. Our rates/costs are specific to the Product/Region, and the rates and costs may change several times during the year.

How do I set up my tables so that when I pull up an old contract, it shows the rates that were in effect when that contract was signed (instead of whatever the current rate is)? It seems like bad design (because of the repetition) to store the rates in the ContractLineItems table. . .


VBAjedi

 

[lespaul]

Well, welcome to the woods (or are we in the neck???)!!

anyway, if you are expecting the rate to change over time, then it is not a bad design to store the rate in ContractLineItems table as well. Think of it like an invoice, you may want to include item price in the invoice detail table because the price of the item changes over time.

Leslie

 

[VBAjedi]

Ok. Now, if I go that route. . . (this will probably expose how new I am to Access). . . what's the best approach to pulling the rates/costs from my Rates table for the line-items on a Contract that was just entered, then storing them in the ContractLineItems table? I'd obviously want that to be automatic.

Would I use a stored query that fires when a line item is entered? Or textbox controls that somehow automatically query the Rates table based on the values in the other textbox controls for the line?

VBAjedi

 

[lespaul]

I would use a stored query that returned the rate based on the contract info.

The user isn't going to change the rate right? Its a standard rate? It can just change over time?

Leslie

 

[VBAjedi]

MOSTLY right. . . the process I'm trying to model is ridiculously complex, with many factors affecting the final rate the customer is charged. We have a standard base rate, that is changed several times a year. I'm pretty sure I can anticipate/model almost all of the exceptions, but I'm thinking it would be smart to allow the user to manually override the generated rate for a given ContractLineItem.

So it will fire the stored query to insert the correct rate, but they will be able to overtype that rate. . .

I guess I'll just need to trigger the stored query from a button or something, so it doesn't overwrite any manual rates the user puts in. . .

VBAjedi

 

[VBAjedi]

Thanks again for the help on this. Have a star!

VBAjedi

 

[PHV]

I guess I'll just need to trigger the stored query from a button or something, so it doesn't overwrite any manual rates the user puts in
You may trigger it in the BeforeInsert event procedure of the form if the rate control is left blank or null by the user.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VBAjedi]

Good thought, PH. That's one of those things that contributes towards an app design that just "makes sense".

Here, have one of those pink pointy thingies. . .


VBAjedi