Tax rate changes by date

[Guest]

I have a simple database for issuing purchase orders. I have a table & form
for the main PO and then another table/subform for the individual items on
the PO. I have 2 fields for the taxes in our Province - Provincial Sales Tax
(PST) and our federal Goods & Services Tax (GST). These are logical fields
if the taxes apply to the PO item.

Our GST just changed on July 1st, and there is rumours of our PST changing
some time early next year.

I have seen a thread for being able to calculate the taxes based on the
effective date of the tax, but I can no longer find it, and I did not
understand it completely.

I have created a table called Tax Rate, which has the fields:
TaxID (primary key)
TaxType
TaxRate
TaxEffectiveDate

How do I calculate the taxes on the individual PO items, based on the date
of the PO?

Thanks in advance for the help!

 

[Allen Browne]

Instead of logical fields for PST and GST (yes/no), use fields where you
store the tax *rate*:
PstRate Number (size Double, format Percent)
GstRate Number (size Double, format Percent)

This solves all the problems: you can:
- Use 0 for a row that is tax-exempt.
- Set the rate to whatever is appropriate at the time.
- Retain the right values for old records when the tax changes.
- Cope with sales in different regions (where tax rates are different.)
- Even handle regions that have different tax rates on differents product
types.

I'm not sure if both rates can apply to one item. If not, it would be better
to use these 2 fields instead:
TaxType drop-down list for "GST" or "PST"
TaxRate Number the rate of gst/pst that applies in this case.