Results 1 to 7 of 7
  1. #1
    PeterS is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    45

    Calculating different Percentages

    Hi

    I have a Query that looks like this in SQL

    SELECT tblCustomer.Customer_Name, tblCustomer.Contact_Name, tblInvoice.Invoice_Date, tblInvoice.Invoice_Number, tblInvoice.Invoice_Amount, tblInvoice.Statement_Date, Now()-([Statement_Date]+30) AS Overdue
    FROM tblCustomer INNER JOIN tblInvoice ON tblCustomer.CustomerID_Pk = tblInvoice.CustomerID_Fk;

    I need to calculate different percentages to apply to different overdue values.

    eg: less than 30 days is 10%
    30-60 days is 15%
    60-90 days is 20%
    90+ days is 25%



    Now()-([Statement_Date]+30) AS Overdue........this is my field for calculating the days for the overdue days.

    Ultimately I would like the query to look something like this:

    Customer_Name Contact_Name Invoice_Date Invoice_Amount Statement_Date Overdue Rate_%
    LAX John 23/10/2018 100 31/10/2018 51 15%
    LIBERTY TIM 12/09/2018 100 03/10/2018 79 20%

    Please can you tell me how to get the "Rate%"....I am very new to SQL so please treat me like an infant

  2. #2
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    as in most cases, many ways to skin this cat. I used to employ some mind bending nested IIF statements but got away from them, preferring code or simpler versions of IIF statements. Now I would have to say I've lost the ability to wrap my mind around them. You could try
    Code:
    SELECT tblCustomer.Customer_Name, tblCustomer.Contact_Name, tblInvoice.Invoice_Date, tblInvoice.Invoice_Number, tblInvoice.Invoice_Amount, 
    tblInvoice.Statement_Date, Now()-([Statement_Date]+30) AS Overdue, 
    IIF(overdue between 30 and 59,15,IIF(overdue between 60 and 89,20,IIF(overdue>89,25))) As Rate 
    FROM tblCustomer INNER JOIN tblInvoice ON tblCustomer.CustomerID_Pk = tblInvoice.CustomerID_Fk;
    An alternate method would be a function that the query calls for the query Rate field. Let's see if that sql works.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Join Date
    Apr 2017
    Posts
    1,673
    Maybe you try to have tblInvoice in query 5 times (aliases like inv, over0, over30, over60, over90) using LEFT JOIN and setting overdue days ranges for every overdue alias in WHERE clause. You select all other values as before using inv as source for values from tblInvoices. Overdue percent will be calculated e.g. as
    Code:
    ..., (-1)*((Nz(over0.InvoiceDate,0)<>0)*0,1 + (Nz(over30.InvoiceDate,0)<>0)*0,15 + (Nz(over60.InvoiceDate,0)<>0)*0,2 + (Nz(over90.InvoiceDate,0)<>0)*0,25) As Rate
    I'd use Invoice_DAte (or Statement_Date) instead of Amount for calculating rate, as there may be invoices with Amount = 0.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Switch([overdue] between 30 and 59,15, [overdue] between 60 and 89,20, [overdue]>89,25, True,0) As Rate
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    What are the True,0 parameters? I don't see them in any example that I found thus far.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Just allows for returning something other than Null if none of the other criteria are met. If expression evaluates to True then return the specified value. True evaluates to True. I ran across it used in an example long time ago.

    However, I just tested and BETWEEN AND is not working. So:

    Switch([overdue]>=90,25, [overdue]>=60,20, [overdue]>=30,15, True,0)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    PeterS is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    45
    Thank you guys

    everything works 100%

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

Similar Threads

  1. Replies: 5
    Last Post: 02-11-2018, 03:30 PM
  2. Trying to get percentages
    By UT227 in forum Queries
    Replies: 2
    Last Post: 09-17-2017, 12:00 PM
  3. How do I query and add percentages?
    By lccrews in forum Queries
    Replies: 3
    Last Post: 08-21-2017, 02:22 PM
  4. Percentages
    By sdel_nevo in forum Forms
    Replies: 1
    Last Post: 03-27-2014, 03:13 AM
  5. percentages
    By sdel_nevo in forum Programming
    Replies: 10
    Last Post: 06-05-2013, 01:00 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