Sales Tax - Microsoft Access Forum - Computer Learning Zone


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Sales Tax

Philip Dirden

17 months ago

Greetings Captain Rost,
With your Tech Source guidance, I am on my way to creating an easy to navigate database for my wife to use. In her office practice she serves clients with beauty services and products. With her website she offers beauty products for sale with shipping and has clients throughout the United States. I am the “Office Manager” keeping track of all the transactions, inventory, sales tax, supplies, repairs, etc.
I am in need of your guidance with correctly addressing the sales tax issue on the Order form our 4 types of Orders:
1)Office (taxable products, non-taxable service and non-taxable shipping)
2)California Web Sales (taxable products, non-taxable shipping)
3)Out of State Web Sales, (non-taxable products, non-taxable shipping)
4)Resale (non-taxable products)
Product Table includes Product, Services and Shipping with Yes/No Taxable box with Yes as default.
California sales tax rates change quite often after election cycles, current tax is 8.875 percent (.08875).
My sub total includes both taxable and non-taxable products/service.
I’ve drove myself crazy trying to figure out how to code this to work properly!
What simple solution am I missing to accomplish this task?
Respectfully,
Philip

Richard Rost @Reply

29 days ago

You are on the right track with the Taxable Yes/No field in your Product table. The key is not to calculate tax from the overall subtotal, because that includes services and any other non-taxable items. Calculate a separate TaxableSubtotal from only the order detail lines whose products are taxable.

For example, your totals query can calculate something like:

TaxableSubtotal: Sum(IIf([Taxable]=True,[Quantity]*[UnitPrice],0))

Then the tax is simply:

SalesTax: [TaxableSubtotal] * [TaxRate]

I would store the tax rate on the Order record when the order is created. Do not just keep one "current" California rate in a table and recalculate old orders from it, or historical orders will change whenever the rate changes. Store the actual rate used, such as 0.08875, in an OrderTaxRate field, and store the calculated tax amount too.

For determining whether tax applies, I would use an order-level field such as TaxExempt or TaxJurisdiction rather than trying to make separate calculations for each of the four order types. For example:

- Office and California web orders: apply tax only to taxable line items.

- Out-of-state orders: tax exempt, unless you eventually have nexus in that state.

- Resale orders: tax exempt, but keep the customer's resale certificate information on file.

One caution: California sales tax, especially regarding shipping and handling, can have details that depend on exactly how the charge is stated and billed. Rates can also vary by local jurisdiction, not just by state. I would confirm the business rules with a qualified tax professional, then let the database enforce those rules consistently.

The practical database design is: Product determines whether the item is normally taxable, Order determines whether this particular sale is taxable or exempt, and Order Detail calculates the taxable portion. That keeps it flexible without making your Order form a Ferengi tax-law episode of DS9.

And sorry for taking so long to respond to this. Once in a while I go back and look for old unanswered questions in my forums, and I just happen to find yours.

Next Unseen

New Feature: Comment Live View