Hi guys, you've been really helpful to me in the past!

I'd like to let my users have the option of changing the sales tax rates if the situation ever arises. I have VBA functions that return grand totals from subtotals and the tax rate is hard-coded.

I've created an Options table where I'm thinking of storing these tax rates, but it seems that I would have to query the database to retrieve the tax rates -- something I would like to avoid if possible.

Does anyone have any ideas on the best way to store a user-editable tax rate in a database, and if you've implemented something similar in your database, what was your solution?

Thanks guys!

Well if it needs to be user-selectable, then it can't be hard-coded.
Well, if you need to store it then I think you're going to need a table. A question I have for you is do you want the tax to be user specific, or something else like state-specific or product-specific, etc?

I don't think it would be too hard to look up the tax rate from a table and use that in your code instead of hard-coded. You can do something even as basic as a dlookup, or better yet an elookup.

Great, thanks a lot. I wasn't aware of these methods. I'm in Canada, so I'm going to need it to include PST and GST. Problem solved though, thanks!

don't forget that PST and GST are disappearing soon!!!

(to be replaced by the HST)

Really? When is that supposed to happen? I'm surprised I didn't hear about that!

EDIT: Figured out why I didn't hear about it -- it's only Ontario!

opps, my bad -- it's only ontario (for now)

CityNews.ca - Toronto's News: GST And PST To Become HST As Ontario Budget Ups Your Taxes

What a horrible time to increase taxes...

Why? It's pretty cheap to hit a small table in a self-contained Access application. If you need ultra-high performance, you may want to look at other platforms...

I would STRONGLY recommend using a tax table so you can:

a) store multiple tax TYPES
b) store multiple VERSIONS of a given tax type
c) delegate maintenance of current tax information to appropriate resources.

Great, thanks a lot. I'm new to database development (Starting my fourth year of Computer Science in the fall; my current project is for a Co-op work term), so I'm still learning the ins and outs. It's been really interesting thus far!

best way in my books is to have a table which identifies the tax rate(s) applicable and store that code against the product

if you have multiple taxes applied to the same sales line then you may need to have multiple flags against the product line

in the UK we (thankfully only have the single sales tax called VAT... there are IIRC 4 rates.. exempt, zero rated, fuel rate and standard rate.) so its a single rate code per product.

one thing to bear in mind if you are coding things like tax rates is how you handle changes in rates, especially if you have to manage invoicing over thsat transition period. so you need to either store a taxrate and validity or when you create an invoice copy the valid taxrate of the moment

I think you need to have a standard rate on Products and the ability to vary Sales Tax on Invoicing.

There should be a Sales Tax file to be selected during the Invoice process, I don't know about the Canadian system but it is a good idea to include the Sales Tax code down on each Invoice line. The Sales Tax value should also be stored on each Invoice line item.

When rates change if you store the standard Sales Tax Value you will need to reprice however wholesale Price changes often follow Sales Tax variations.

Simon