Share via

Microsoft Access: How to calculate tax?

Anonymous
2021-11-21T22:06:26+00:00

Microsoft Access, have created a table with stats and dollar values, need to create a calculation to add GST (15% tax) in a new column. Struggling to create the expression with a percentage

Microsoft 365 and Office | Access | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. HansV 462.6K Reputation points MVP Volunteer Moderator
    2021-11-21T22:11:34+00:00

    Adding 15% is equivalent to calculating 115% = 115/100 of the amount, in other words: multiplying with 1.15.

    Let's say the dollar field is named PriceEx.

    The price including GST is given by

    PriceIn: [PriceEx]*1.15

    7 people found this answer helpful.
    0 comments

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. ScottGem 68,810 Reputation points Volunteer Moderator
    2021-11-22T03:19:54+00:00

    The result should NOT be stored. If the GST is fixed, then just calkculate the resulkt on the fly. If the GST can change after time, then you store the current GST in a separate table.

    0 comments
  2. Anonymous
    2021-11-22T03:09:58+00:00

    I'd add one point.  The rate of tax is data.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.  So, if the rate is variable per product, as with Value Added Tax (VAT) for example, it would be stored in a column in the Products table, and assigned to a column in an InvoiceDetails table or similar when an invoice is raised.  When computing the gross price, therefore, the expression would be:

    GrossPrice: [NetPrice]*(1+[GST])

    The GST value should be stored as a fractional value, 0.15 in this case, but can be formatted as Percent.

    I don't know how it works with GST, but for VAT, the tax would only be added in the above way for retail transactions.  Otherwise it would be added to the total net price for each group of line items which attract the same tax rate.  For how to compute the tax in this context you'll find an example in InvoicePDF.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    This little demo file is primarily intended to illustrate how to generate a report (the invoice) as a PDF file, but happens to use a simple hypothetical invoicing database.

    0 comments
  3. Anonymous
    2021-11-21T22:38:47+00:00

    HansV MVP, you are a legend! I was pulling my hair out over that one, great explanation. Thank you

    0 comments