Dealing with Sales Tax and GST.

[Xenos132]

Since taxes can and will change I would like to save the tax rate in my orders table. I was thinking of having a table called tax and in that table having a sales tax and GST rate. How can I take the data from this table and have it automatically populate my orders table via my order form?

 

[THWatson]

Xenos132
There is more than one way you can do this.

1. In the Orders form, use a DLookup to get the current GST and PST from your tax table.

2. Make a query in which you pull info from both your customers table and tax table, and base your Orders form on that. (unless you find the query is not updatable)

By the way, you may apply a Start and End date to the GST and PST rates.

As an aside, you really think Harper will lower the GST?

Tom

 

[Pampers]

Hi Xenos132,

If you want to store a tax rate with the order, you can set the default value property of the control to:
=DLookUp("[TaxRate]";"[lstTaxRate]";"[TaxRateID] = 1")

When the rate changes, it will automatically use the new rate for the new order, while the old orders will hold the old taxe rate.


Pampers
Just became father!

 

[Xenos132]

Thank you.. pampers, I know it's against normalization so finding this information has been tough.

 

[Pampers]

Hi Xenos132,
I dont think storing a value to make a calculation is violating 3N. It is storing the outcome of the calculation which is not necessary. You are just storing different prices for the same article over time, namely a % for a certain tax.


Pampers
Just became father!

 

[MichaelRed]

It is actually quite "Normal" to do it by storing the current values of information. Actually, you SHOULD be storing most, if not all, of the order and order details for each order as they occur. Even the name, address and other information of "who" ordered the item(s). At some point much of this may change, but you should maintain a record of the information which was actually used for the transaction.




MichaelRed

 

[Xenos132]

Michael, I really wanted to keep a record of the calculated total value on the table as well as I felt it’s was an important number, however I had no success in finding how to store this data and gave in to normalization.

 

[MichaelRed]

... didn't mean to say to storte the recordfs calculated values, just the parts necessary for the calculation. e.g. might want to store cost and mark up to calculate retail, realizing that either cost or mark up may change for the item in different POs (there might even be a different mark up for the same customer, depending on the specifics of the purchas, such as different mark up based on quantity of item purchased). If ALL the components necessary to recreate the total set of fields used are saved, it is possible (and prefered) to NOT save the calculations themselves. It is also QUITE IMPORTANT to make sure the historical record is NOT altered after it is "published". e.g. when you create a purchase order or sales receipt, and that is sent out (to supplier or customer) it (or at least the components as noted above) MUST be preserved as SENT. Otherwise any dispute between the supplier / customer is sure to favor the other party, as they will have the "hard copy" and you will have a data integrity (and therefore a credibility) problem.




MichaelRed