Results 1 to 7 of 7
  1. #1
    ReadFree is offline Novice
    Windows 8 Access 2007
    Join Date
    Jul 2016
    Posts
    3

    Calculating Sales Tax in a Report but Excluding Certain Products....like mailing fees

    Hi,



    I'm working with an already created Access Database (2007) that I didn't create. It manages our orders for certain products. I've been asked to change the database so that when the main user generates the Reports that are bills to be sent out, they include a line for sales tax. However, Sales Tax (.08 in this case) cannot affect certain items - in this case, Mailing fees. I'm curious as to what expression statement would enable me to do this... Does anyone have any insights?

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Does anyone have any insights?
    -make a backup of your production database before you start (even 2 copies and put them in a safe place)
    -it won't be 1 statement in all likelihood
    -who knows which products are sales tax exempt? Suggest you have a list of Products and identify which are sale tax exempt.
    -Are all affected products taxed at 8%?
    -where is the original programmer/designer of the database? Who maintains/adjusts it?
    -How critical is this database to your business?
    -How did you get "volunteered"?
    -What is/are any associated deadlines?
    -What is the general approach you are considering?

  3. #3
    ReadFree is offline Novice
    Windows 8 Access 2007
    Join Date
    Jul 2016
    Posts
    3
    Hi,

    I've made the backups already.

    There are only two items excluded from sales tax which is consistently 8%. And I do have a list of all products.

    The original developer is long gone. Maintenance is sketchy.

    Critical

    Deadline - August 15

    I'm considering an IIF statement.

  4. #4
    NightWalker's Avatar
    NightWalker is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Apr 2016
    Location
    Midwest USA
    Posts
    253
    You could add a Taxable field in the products table. then in query Where statement put the exception to not calculate tax on those items. This would be the direction I would probably try first but I am fairly new at all this myself. I would need to know more about your code to see where it is doing the calculations and when it adds in the shipping fees that are not taxable.

  5. #5
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Tax should also be on the line item in the table. Depending on the item sold, tax can be zero or the amt. This rate is ALSO on the line item.
    print with these.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do you have samples of the report (as it is) and (as you want it to be).


    From another post:
    On your SalesDetails table, record the SellingPrice and Quantity. Do NOT use the Price as it is retrieved from the Products table--because, if you do that, and change the Price in the Product table, the Price on all of your old invoices/orders will change. Instead, store the current price and quantity on the SalesDetails record. This will also let you have Loyalty Programs, Specials, ClearanceSales etc . In reality, this is the Price you charged per Product for this Quantity of Products to this Customer on this Date -- it becomes a permanent record.
    I like to call this the AgreedTo or AgreedUpon Price.

    Your Product table should have a field eg Taxable Item. If you foresee a possible change such that soe items would have different SalesTax percentages, you could design the table now. This could get tricky because you may have to have a field(s) to show the Date this SalesTaxPercentage Started and ended to preserve your historical data.

    On your SalesDetail record you could include

    ProductID/ProductName
    AgreedToPricePerUnit
    Quantity
    SalesTaxAmount

    IIf(productId is Taxable, ((AgreedToPricePerUnit * Quantity)* .08), 0)

  7. #7
    ReadFree is offline Novice
    Windows 8 Access 2007
    Join Date
    Jul 2016
    Posts
    3
    Click image for larger version. 

Name:	report.PNG 
Views:	30 
Size:	35.2 KB 
ID:	25313This is the design view of the current report. I've added a field in the associated table (1costs) for TAXABLE as a check field.

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

Similar Threads

  1. Replies: 1
    Last Post: 10-18-2012, 12:01 PM
  2. Replies: 8
    Last Post: 08-24-2012, 01:54 AM
  3. Replies: 8
    Last Post: 04-24-2012, 01:05 PM
  4. Replies: 1
    Last Post: 08-11-2011, 01:52 PM
  5. E-Mailing A Report (From a form)
    By adams.bria in forum Forms
    Replies: 3
    Last Post: 04-30-2010, 10:03 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