Results 1 to 12 of 12
  1. #1
    Nanabush is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    11

    Multiplying two fields in a report depent on the value in a third field

    Hello!
    Im new here a kind of a novice in access. I have created a Invoice report.
    I have managed to add txt fields that calculate the sum wit and without the VAT.

    In sweden we have 3 different VAT rates. 6%, 12% and 25%



    I want to show the sum of my two fields [Quantity] and [PriceNonVat] when the value in field [VatPercentage] is for exapmle 12%

    How do I do this? I want the report to be able to handle all three different VAT's in the same report

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What determines which VAT rate applies to a product/service?

  3. #3
    Nanabush is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    11
    I have a field in the my table tblProduct thats called VatPercentage. So I have predefined tha VAT-rate for every product

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    When dealing with an Invoice, there are usually more tables involved. A Customer places an Order for a quantity of 1 or many Items. Each Item has a SellingPrice. Depending on how many of each Item is purchased, the amount to be Invoiced is the sum of
    ((Quantity * UnitPrice) + related VAT).

    See this data model for more info in context. See where the VAT is applied.

    Also, since the Price of an Item can/will change with time, do not rely on the ItemPrice in the ItemTable for your calculation.
    Store the quantity and "the AgreedUponPrice" in the Order Details table. This way you will not change historic records when you change the ItemPrice in the ItemTable.

    **"the AgreedUponPrice" is the Price you charged for that Item on that Date for that Customer. It i a record of the transaction and will not change if/when the ItemPrice changes.

  5. #5
    Nanabush is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    11
    That sounds like a good plan. But back to how I can get the sum when multipling quantity and AgreedUponPrice when the VAT is 12% and so forth?
    Is it possible to do this formula in a calculated field?

  6. #6
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    In general, this sort of loop
    Code:
    For Each Item in the Order
     ItemTotalPrice = ItemQuantity *ItemPrice* VATForItem
    
                    = 10items * $6.00/item * 1.12
                    = $60 *1.12
                    = $67.20
    OrderTotal = OrderTotal +ItemTotalPrice
     Next Item
    The 1.12 represents a 12% VAT.....

  7. #7
    Nanabush is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    11
    I've done that but what I am after is the bottom of an Invoice or a reciept where you sum all the rows where the field VatPercentage is 12% and then sum all the fields where the VatPercentage is 25%.
    These should be presented lite this...

    12% Price Without VAT VAT-total 12% Total Price
    25% Price Withou VAT VAT-total 25% Total Price

    You get the idea....

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Do you have some sample data? A copy of the database with enough records to show the issue?
    A picture/sample of the invoice you are trying to present?
    Attached Thumbnails Attached Thumbnails sampleInvoiceWithVat.png  

  9. #9
    Nanabush is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    11
    Click image for larger version. 

Name:	invoice.PNG 
Views:	14 
Size:	43.8 KB 
ID:	41805
    In this example there is only 12% VAT but if there had been, not all these rows would summed where the arrows are. Some would have been summed in the row below.

    In your example and in mine there are a total of all the different VAT's in the bottom right corner but I want to have them separated by VAT-rate as well.

  10. #10
    Nanabush is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    11
    Click image for larger version. 

Name:	db overview.PNG 
Views:	16 
Size:	22.1 KB 
ID:	41807

    So in the textfield in the report I want to take =SUM(Quantity*AgreedUponPrice) Where VatPercentage = 12%

    I cannot put this in a correct expression (Not my strong suite)

  11. #11
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  12. #12
    Nanabush is offline Novice
    Windows 10 Office 365
    Join Date
    May 2020
    Posts
    11
    I'm sorry to say that did not help me. There has to be a way to multiply two fields dependent on a third. Maybe I should try the VBA section?

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

Similar Threads

  1. Replies: 9
    Last Post: 02-05-2018, 09:45 AM
  2. Replies: 3
    Last Post: 07-01-2016, 08:11 AM
  3. Replies: 1
    Last Post: 06-22-2012, 06:55 AM
  4. Replies: 1
    Last Post: 11-02-2011, 11:52 PM
  5. "multiplying" fields
    By werfnerf in forum Queries
    Replies: 3
    Last Post: 05-27-2011, 01:59 PM

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