Best way to calculate sales tax

[okiekaren]

I tried to buy two point of sale systems and both had issues. Meanwhile my girls use handwritten tickets. At the end of every month, I have to enter all the sales individually in Excel. ENOUGH. I now want to write a small Access database and have my staff enter the tickets on a daily basis. Most of the time, we calculate sales tax at .0875. But there are occasions when someone is tax exempt. So, I set up a field in a table that says 'SalesTaxCollected' and made it a yes/no field. Now, I would like to say something like the following: IF sales tax was collected, then RetailPrice * Quantity * TaxRate. But if none is collected, I would like it to just reflect RetailPrice * Quantity. The info is being pulled from a Query that includes ItemID (and related: tblInventory) and tblSales and tblTax). Thank you.

 

[Phildaburn]

What would you want to call this field? The formulas you're using are for 2 different things. The first one that includes TaxRate will yield only the tax amount - the other formula yields a total price for those customers you are tax exempt. Another question: in situations where a customer claims to be tax exempt are you recording their tax exempt certificate number? If not, you may want to check with your company auditor to make sure you're GAP compliant.

So, to help you with 2 formulas, here is the following:

TaxCollected: iif([SalesTaxCollected] = -1, [RetailPrice] * [Quantity] * [TaxRate],0)

Total: iif([SalesTaxCollected] = -1, ([RetailPrice] * [Quantity]) + ([RetailPrice] * [Quantity] * [TaxRate]), [RetailPrice] * [Quantity])

Hope this helps...
Phil...

 

[boblarson]

I created a POS for a hospital in the Portland area and we had to deal with multiple sales tax rates. So, what I did was to have a default value show up if the person's address was within a certain city/state combination and the field was populated by an after update event of a combo box which had the items available to purchase. The reason I had to do it for each individual item was that potentially they could have partial orders that needed sales tax applied and part of them not.

There are a multitude of ways to do it all but as for the calculation, you can just use the same calculation for all of them, if you just have the nulls accounted for using the NZ function (which would be on everything just in case) and then it wouldn't matter. If there was, it would be added, if there weren't it wouldn't:

(Nz([RetailPrice],0) * Nz([Quantity],0) + (Nz([TaxRate],0)) * (Nz([RetailPrice],0) * Nz([Quantity],0))

 

[boblarson]

you may want to check with your company auditor to make sure you're GAP compliant.

Phil:

That is GAAP (Generally Accepted Accounting Principles)

 

[Phildaburn]

Thanks, Bob. I've heard lots of people saying what sounds like "gap" and I knew it was an acronym.

Phil...