Results 1 to 8 of 8
  1. #1
    Stephanie53 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    246

    Caculate Taxes for Non Tax Exempt Customers no Quote/Order Report


    i want to be able to add taxes and have it caculate to the customer if they are not tax exempt and if they are to have .0825 if they are.

    on these tables i have the following:

    Customers: 1 field named Tax Status which lists each customer either as a "1" (which means Tax Exempt) or a "2" (which means Taxable)

    Orders: I have 2 fields - Tax Rate and Tax Status

    have 1 query named Order Summery

    Now on the Quote/Order Report that is sent to the customer i want it to take the subtoal of the order - look at the customer and the order - and add taxes in the amount of 8.25% to those that are not exempt and make it $0.00 if they are exempt

    can i put it in as Control Source =Nz(Sum([ExtendedPrice]...but i cant figure out how to put in the rest of the code to and read to which table or query...

    can anyone assist on this

    TIA

    Stephanie

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Can you do it if you use invisible controls on your report? Each control on a report can do running sums if you want.

  3. #3
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    I'm assuming that your 8.25% in your question would be in the field [Orders].[Tax Rate].

    If so, here's the term for your tax rate - IIF([Orders].[Tax Status] = 2, [Orders].[Tax Rate], 0)
    or, if you just want the 8.25%, use this - IIF([Orders].[Tax Status] = 2, .0825, 0.0)

    Multiply that by your subtotal field.

  4. #4
    Stephanie53 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    246
    ok i have put that into the control source on the Quote?Order Report but its not allowing it to do any caculation at all. i would send you the DB but you have 2003 - and due to some macros i have set up i am not able to convert it to earlier version...

    is there anyone else that can help that has 2010 version that can see what i am trying to do?

    i do thank you for trying to help me though

    Stephanie

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    If you want to Compact and Repair the db and then zip and post it, I bet someone will be able to look at it. Remove any sensitive data of course.

  6. #6
    Stephanie53 is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Mar 2013
    Posts
    246
    Database551.zip

    Here is the DB if anyone can help me with this...

    TIA

    Stephanie

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    To start, here's a link to review: http://access.mvps.org/access/lookupfields.htm
    Then I would ask why the [Tax Rate] field of the Orders table was not included in the RecordSource query of the Order/Quote report?
    Then why do you format the ExtendedPrice field of the RecordSource of the Report. You know that turns it into a Character String right?

  8. #8
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742

    A few suggestions

    Looks like you're kind of in the middle of a redo of the data, and need to run a number of update queries to set the initial values on Tax Rate, Tax Status, and so on, for the test data on the Orders table.

    UPDATE QUERY FOR TEST DATA

    Assuming that there was already data in the [Tax Status] and [Tax Rate] fields on the [Orders] table, here's an example update query to set the [Taxes] amount, based upon the [Price Total] in the [Order Price Totals] table.

    Code:
    UPDATE [Orders]
    SET [Orders].[Taxes] = [Order Price Totals].[Price Total] * [Orders].[Tax Rate]
    WHERE [Orders].[OrderID] = [Order Price Totals].[OrderID]
    AND [Tax Status] = 1
    ORDER DETAIL CHANGES

    You need to determine when you are going to calculate the Taxes on the order - IMHO It seems like it belongs on the Order Details Screen, and should be recalculating after any Order Detail change at the same time as the Total Price column is recalculating. On the other hand, there is a good argument that Taxes should have its own tab just like Shipping.

    INVOICE CHANGES

    Your invoice should have a line near shipping to pick up the value from that [Taxes] field, just like you are picking up the [Shipping Fee] field, and your [Invoice Total] field at the bottom of the Invoice should be updated to add that in as well.

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

Similar Threads

  1. update query that adds 8.25% taxes
    By alinapotter in forum Queries
    Replies: 2
    Last Post: 04-09-2013, 07:59 AM
  2. Need to caculate in Access
    By Stephanie53 in forum Access
    Replies: 36
    Last Post: 03-27-2013, 07:19 AM
  3. Count unique customers in report header
    By hithere in forum Reports
    Replies: 7
    Last Post: 02-08-2013, 12:47 AM
  4. Replies: 6
    Last Post: 12-07-2012, 07:57 AM
  5. Auot caculate
    By rovman in forum Programming
    Replies: 8
    Last Post: 12-11-2011, 07:58 AM

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