Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2009
    Location
    British Columbia, Canada
    Posts
    29

    Unanswered: How to store tax rates

    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!

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by nfw04
    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.
    Well if it needs to be user-selectable, then it can't be hard-coded.
    Quote Originally Posted by nfw04
    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?
    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.
    Me.Geek = True

  3. #3
    Join Date
    Jun 2009
    Location
    British Columbia, Canada
    Posts
    29
    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!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by nfw04
    I'm in Canada, so I'm going to need it to include PST and GST.
    don't forget that PST and GST are disappearing soon!!!

    (to be replaced by the HST)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2009
    Location
    British Columbia, Canada
    Posts
    29
    Quote Originally Posted by r937
    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!
    Last edited by nfw04; 07-04-09 at 14:10.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002

  7. #7
    Join Date
    Jun 2009
    Location
    British Columbia, Canada
    Posts
    29
    What a horrible time to increase taxes...

  8. #8
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by nfw04
    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.
    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.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  9. #9
    Join Date
    Jun 2009
    Location
    British Columbia, Canada
    Posts
    29
    Quote Originally Posted by Teddy
    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!

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Sep 2006
    Posts
    265
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •