Taxes Please help?

[Lin]

Thanks so much in advance.

I realize you all volunteer your time and it is much appreciated.

I have read Allen Brown's tips, and many other examples i.e North wind DB
etc...
But I am missing something here. First there are few to none examples of
Service.
We do both, Service primarily, and very little sales. One customer is
non-profit fees are less.

Ultimately The idea many have suggested would be not to store tax in i.e
products table for good reason. In my case it would be a service table. In
the NW db it shows (freight) in Orders. As I would be charging a rate based
on each order, not per product or service. And what if the rate of tax
changed from 7 - 9% ex.

I would need to store this info somewhere. Would it be adviseable to create
a tax table OR store in the Orders table, Some would require two taxes GST,
PST.
Or should I calculate on a form and After update back to a table????

Like I say there appears to be varying ideas on the subject even amounst the
pros.

Our company for example is primarily service, therefore we pay the GST
collected
back to the Govt at tax time, so we need it stored somewhere.

Any and all help would be very much appreciated. Thanks to all those who
help us.

Kindest regards.

 

[Ken Sheridan]

The tax rate on any service or goods supplied would in a simple scenario be
in the Orders table as you say it is applied per order, not per item. Also
if the rate changes you want existing orders to keep the rate used at the
time the order was created, not to take on the new rate. In the language of
the relational model its said to be functionally dependent on the key of the
Orders table. There should also be a table of tax rates which can be used to
get the current rate of tax. This would have one row per rate currently in
force, so if there are 3 rates of tax each applying to a different type of
service provided, the table would have 3 rows. This table would be edited as
rates change. If the rate is selected manually when an order is created all
you'd need would be a combo box on the Orders form bound to the Orders
table's TaxRate column, getting its list from the TaxRates table with a
RowSource such as:

SELECT TaxRate
FROM TaxRates
ORDER BY TaxRate;

If the rate applied is determined by some other field such as ServiceType
then you'd need a ServiceType column in the TaxRates table and when the
ServiceType value is selected in the Orders form a value would automatically
be inserted into the TaxRate field by looking up the rate for that service
type from the TaxRates table. The way the Order Details Subform looks up the
UnitPrice from Products in the Northwind database is analogous to this.

You would not store a gross amount in the Orders table but compute this from
the net amount and the tax rate either in a computed control or in the
underlying query: gross amount =
net amount * (1+TaxRate), where TaxRate is expressed as a fractional value,
e.g. a 7% tax rate would be stored as 0.07 in the TaxRate field.

The above is a simple scenario but as some orders will attract two taxes you
should have the TaxRate column not in Orders but in a separate table,
OrderTaxes say, with columns OrderID, referencing the primary key of Orders,
TaxType and TaxRate. You can then have as few or as many taxes applied per
order as necessary. The taxes would then be entered via a subform in the
Orders form. The TaxRates table would also need a TaxType column.

Ken Sheridan
Stafford, England

 

[Lin]

Thank you Ken, I will give that senario a shot!

Cheers!