handle different sales tax rates for same item based on location.

[CP]

We have created a database for the purpose of making quotations. We store
the quotation details in a table quotedetails and draw the informaiton on
certain parts from a table called partdetails, this table has a fixedcost
and variablecost field to account for differences in length of some cables
used.

The quotations are being made for different states and provinces each of
which have different sales tax rates which may or may not apply to some or
all of the parts listed in a quotation. I think I need a table that lists
each state/province and the appropiate sales tax and then use this to apply
that rate to the product based on a check box on the quotation form that
would be used if taxes are applicable to each product.

If anyone has experience with a similiar problem would appreciate any input
on the best approach to take
Thanks
Chris

 

[Henry]

You are going in the correct direction. You have a
significant table/relationship design problem that can be
solved with a little work and a correct approach to table
design, relationships and data normalization. For
example, you would not normally put into one table
states, provinces and tax rates. States and provinces
don't change , but tax rates do. Secondly, not all tax
rates apply to all parts. Some tax rates apply
differently, etc., etc.
You apparently have a good idea of what you want out of
the databasae, now figure out what data needs to be
stored. Then normalize the tables and establish correct
relationships. There is a well established DB design
approach that should be followed to achieve your desires
with the least amount of effort. Not to say your effort
will be small to achieve a good DB application. I have
been there and done that.
Send me an Email and I will send you a few articles about
normalization and perhaps help you avoid major problems.
(e-mail address removed)
Cheers,
Henry