Results 1 to 6 of 6
  1. #1
    Rhodan is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    23

    Red face POS and two taxes

    Working on a fairly simple point of sale system for a volunteer organization but my brain is starting to melt.



    There are two taxes here and each product can have either one, both, or none. If that's not bad enough I have to break out the taxes for reporting. So rather than a fixed amount typed into each product I figured I'd put the tax rates into a "singleton" table that holds the organization's information. That table only has one row.

    Click image for larger version. 

Name:	accesspos.jpg 
Views:	16 
Size:	41.0 KB 
ID:	39380

    So the "Has" fields are intended to be used when creating the invoiceline record. The idea being that if it's true then calculate the mount as the line subtotal time the properties tax rate.

    Since prices and tax rates change I thought it best to calculate and store the taxes with the invoiceline so that changing the product price or tax rate later wouldn't change the invoice amounts.

    Where I'm running into brain destruction it figuring out how to use the fields from the singleton table that has no relations with the invoicelines.

    My question is whether or not this is a good way to handle the taxes or whether there's a better way to store and apply the tax rates? If this is a good way then my next question about getting the value from the first row of an unrelated table will be asked in the queries sub forum

    Thanks!

    Edit: Sorry, can't figure out how to remove the extra attachment....
    Attached Thumbnails Attached Thumbnails accesspos.jpg  

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    You can have queries without joins (they are called cartesian queries) - queries are not the same as relationships

    If the propertiesT table only has the one record, you can just do the calculation when required. If it has more, you need a basis for filtering it down to one - I would expect to see a propertyFK field in the Invoices table.

    I would also store the priceper in the invoice line table since that can change as well

    No need to store the subtotal and line total - they can be determined when required

  3. #3
    Rhodan is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    23
    Ah, fkOrganization in the invoice now seems obvious. I just have to figure out a way to set it for the invoice so the person creating them doesn't have to. I could fudge it and just manually set the ID in the form code and probably will for now. I can figure out an automated way later if I want to have more than one organization.

    The priceper missing from the invoice line was me not getting there yet. That was my intent but I was fixated out how to get the taxes incorporated.

    Very helpful. Thanks!

  4. #4
    Rhodan is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    23
    Actually I just realized I can't calculate the taxes per invoiceline because of rounding. I'll have to do that at the invoice level.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not sure what you mean - most taxes apply at the item level and should be calculated at that level - so I would check the requirement with your tax authority who should specify how they should be calculated. There is no need to store the values at the invoice level - again, they can be calculated when required.

    with regards rounding, recommend you use the currency datatype and round to 2 decimal places when calculating - you tax authority may have something to say about that as well.

  6. #6
    Rhodan is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    23
    I checked it looks like they are at the item level so that makes things easier.

    I think what I'll do is store the tax rates in the invoice, HasXST flags in the invoicelines and use calculated results for the applicable tax(es). That way the invoice and line items are disconnected from products and tax table yet I can still separate the tax amounts for reporting and refunds on line items won't require the operator to grab a calculator.

    Actually... I'll just calculate the tax amounts per line and store them there. Makes for a larger table but if this volunteer group manages 1000 invoice lines a year in this rural area they'd be wizards! Heck, even 10,000 invoice line items per year isn't going to stress anything.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 10-06-2015, 04:32 PM
  2. Replies: 7
    Last Post: 06-04-2013, 11:14 AM
  3. update query that adds 8.25% taxes
    By alinapotter in forum Queries
    Replies: 2
    Last Post: 04-09-2013, 07:59 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums