What about Sales Tax?
Brian Cornellison
run reports, open forms, and calculate Invoice totals that all include a
common variable - THE SALES TAX. But instead of referencing the actual
number in about eight different forms, is there an easy way to reference the
Sales Tax in a separate table or piece of code, and refer to it from every
report and/or form that I have? That way, if the sales tax rate ever
changes (which it did recently in my county), then it only needs to be
changed in one place... Any help would be MUCH appreciated.
Don P. Mellon
tax rates . One table has fields for State Abbreviation, State Name, and
State Rate, which holds the sales tax percentage. The second table holds
local data, such as Local Code, Local Name, Local Rate. (The Local Code is
a numeric identifier that can be used to group local sales taxes collected
by specific taxing authorities, e.g., MyTown and YourTown might collect
sales taxes that are paid to the same taxing authority but the rates are
different in our towns.)
In the Orders table, for each customer order there are two fields that
capture the state and local sales tax rates for each order. (Do not make
the mistake of attaching a sales tax rate to the customer because most taxes
are based on the delivery point of the order, not the originating point or
the customer's address, which might be different from the ship-to address.)
When an order is processed, the ship-to state determines what state sales
tax rate to apply, and the ship-to city determines what local sales tax rate
to apply. These taxes are calculated in a query and displayed in the
invoice report.
Not all products sold from this database are taxable, however, so each
product record in the product table has a Boolean field to indicate whether
to apply any tax rate to the product.
For maintenance, the State Rate, Local Rate, and Product Taxable (Yes/No)
data must be kept up to date, but this is not a big job since the rates only
change during election years. The system can be adapted to any level of
sales taxation by adding more tables.
"Brian Cornellison" <bri...@home.com> wrote in message
news:zUFD6.14601$J8.12...@news1.rsm1.occa.home.com...
Don P. Mellon
I forgot to also mention that not all customers in the database must be
taxed. Some customers are tax exempt because they are "self-reporting"
corporations (they make lump-sum sales tax payments to the state at the end
of each year), and some customers are government entities that pay no taxes.
So, in the Customers table for each customer record there is a Boolean field
for Exempt (Yes/No). The query that calculates sales taxes for the invoice
has a criterion set to No for this field, as I recall.
"Don P. Mellon" <wig...@3rivers.net> wrote in message
news:ulGD6.14743$u7.53...@e3500-chi1.usenetserver.com...
Mark A
news:zUFD6.14601$J8.12...@news1.rsm1.occa.home.com...
That imho is an application for a related table which links to your main
invoice or customer table on qualifying fields.
These qualifying fields would lead you from the invoice or customer table to
a suitable tax value in the related tax table.
You could then load and calculate based on that related value.
I would expect you should transfer and store completed invoices separately
(such that they do not contain calculated fields) as otherwise if or when
you do change tax rates the new rate will be applied to your past invoices
recalculating them and causing your historic tax and invoice records to be
corrupted.
--
Mark A.
Monthly Marketing Mag @ www.sticky-marketing.com
Allen Browne
--
Perth, Western Australia
Tips for MS Access users at:
http://odyssey.apana.org.au/~abrowne
Larry Linson
overlapping (e.g., a state, county, and city sales tax may all apply to a
sale either at or to a given location), anything more than a "very local"
application requires a massive table, frequent updates, and a great deal of
effort.
I recently discussed a situation with a client (who is part of a humungously
large corporation) who have to charge proper sales tax for almost every
community in the U.S. They had contracted with one of the Big Five
Professional Services firms to gather the data and maintain the table, which
they refreshed each billing period before calculating the bills. It was not,
I can assure, you, an inexpensive undertaking considering the hourly rate of
the Big Five.
"Brian Cornellison" <bri...@home.com> wrote in message
news:zUFD6.14601$J8.12...@news1.rsm1.occa.home.com...
Doug Hutcheson
You have received some good advice already.
My $0.02:
1. Each product belongs to a Tax Rate Group. (0, 10%, 20% etc) There may
be several groups per product, for eg City, State and Federal taxes.
2. Each customer belongs to a Tax Charge Group. (Exempt, Wholesale,
Retail etc)
Each of these groups is represented by a table.
Use the tables to identify the tax liability for the sale of this item
to this customer.
Once the sale is made - ie, when creating the invoice item record - copy
the existing details from the tax tables into appropriate fields in the
invoice item record, so that later changes in rates or liability do not
cause your existing sales records to go out of whck - if you charged 10%
tax last August, you always want your August invoices to report this,
even if the rate changed to 15% in December.
Use a similar model for other price variance factors, such as discounts.
HTH
Doug
MShe...@compuserve.com
<larry....@ntpcug.org> wrote:
>I recently discussed a situation with a client (who is part of a humungously
>large corporation) who have to charge proper sales tax for almost every
>community in the U.S. They had contracted with one of the Big Five
>Professional Services firms to gather the data and maintain the table, which
>they refreshed each billing period before calculating the bills.
Years ago, when I was a freelance photographer, one community in
Missouri published a sales tax card with a few errors in it.
Businesses were required to collect the sales tax shown on the card,
not the sales tax a correct calculation would have given you.
Sales tax is an odd beast.
--
Mike Sherrill
Information Management Systems