For B.Reynolds Re: Taxes

[Guest]

Read your post re taxes. I had similar problem. I also used
a Y/N on form. However, you are right. My question is actually one but for
many instances.
How would you set up a query, say if your tax rate changed, or
Fee for service changed, or product etc...
You'd want the most recent amount to reflect on the form.
Is there a good sample DB out there to show how this works?

I have purchased and read a few books, but they are limited on the examples.

Any help would be greatly appreciated.

Cheers!

 

[Brendan Reynolds]

I'm afraid I don't know to which post you are referring, Lin, and without
any context I can't give any very specific answer.

Generally speaking, there are two possible approaches to this sort of thing.
You can store the rate along with the record of the transaction, or you can
maintain a table or rates and their effective dates.

The Northwind sample database illustrates the first approach. The products
table stores the unit price of each product, but the order details table
stores the unit price as well as the product ID. If unit price never
changed, this would be in breach of the principles of normalisation, as unit
price would be dependent on product ID. But because unit price is subject to
change, storing the unit price in the order details table makes sense. The
unit price in the product table and the unit price in the order details
table are not the same thing. The unit price in the product table is the
current unit price, while the unit price in the order details table is the
unit price when the order was taken. The unit price in the products table
can be changed without changing the unit price in existing order detail
records, which is as it should be.

I can't think of a readily available example of the effective date approach.
Perhaps someone else may know of one?