Results 1 to 12 of 12
  1. #1
    Coryjacques is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    16

    Capping an equation at a certain value

    Hello,

    I need to cap off a few equations below at certain values:

    Code:
    SELECT Personal.employeenumber, Company.AnnualSalary as [Salary], personal.dateofbirth AS DOB, FORMAT((#01/24/2016#-[DOB])/365.2, 0) AS Age, FORMAT(ben.annualsalary*2, "Currency") AS [Basic Life], "" as [Basic Life Cost], FORMAT(ben.annualsalary*2, "Currency") AS [Basic AD&D], "" as [Basic AD&D Cost], FORMAT(vb.EVOL, "Currency") AS [Supp Life], format(vb.svol, "Currency") AS [Spouse Life], FORMAT(((vb.dvol*0.039/1000)*12)/26, "Currency") AS [Child Supp Life], FORMAT(((vb.VADD*0.04/1000)*12)/26, "Currency") AS [Supp AD&D], FORMAT(((vb.vadds*0.04/1000)*12)/26, "Currency") AS [Spouse AD&D], format(((vb.vaddc*.04/1000)*12)/26, "Currency") AS [Child AD&D], format(ben.annualsalary/12, "Currency") AS LTD, "" as [LTD Cost], FORMAT(ben.annualsalary/52*0.7, "Currency") AS STD, "" as [STD Cost]
    FROM ((Personal 
    LEFT JOIN Ben ON Personal.Name = Ben.Name) 
    LEFT JOIN VB ON Personal.name = vb.name) 
    LEFT JOIN Company ON Personal.name = Company.name
    WHERE (personal.homecompanycode = 'EVRB' AND personal.FTPT = 'F' AND personal.employmentstatuscode <> 'T' AND employeetypecode = 'REG') 
    OR (personal.homecompanycode = 'EBPS' AND personal.FTPT = 'F' AND personal.employmentstatuscode <> 'T' AND employeetypecode = 'REG');
    I need to cap Basic Life and Basic AD&D at 350000, LTD at 20833.33, and STD at 2000... but I have no concept of how to do this!



    Can you please help?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a table of your caps:

    Basic AD&D , 350000,
    LTD , 20833.33
    STD , 2000

    the add the table into the query joined on the code,
    make 2 queries,
    Q1 to add all values <= limit
    Q2 to add the limit since they went over.


  3. #3
    Coryjacques is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    16
    Sadly that might be above my skill level to work out on my own - I've only ever really worked with one query at a time, and I'm not entirely sure how to add multiple.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Simply replace the appropriate parts of your original query with something like this:

    format(iif(ben.annualsalary/12>20833.33,20833.33,ben.annualsalary/12), "Currency") AS LTD

    Cheers,
    Vlad

  5. #5
    Coryjacques is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    16
    That's excellent! Do IIF statements work pretty much the same as IF statements from Excel? [If(criteria,value if true,values if false)]?

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101

  7. #7
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Keep good notes, because you're hard-coding business rules into your query. When a limit inevitably changes, you'll need to change it everyplace you've done this.

    I might use a single-record table with fields for each of those limits. Include it in your query and change Vlad's formula to:

    format(iif(ben.annualsalary/12>LTD_Limit,LTD_Limit,ben.annualsalary/12), "Currency") AS LTD

    where
    LTD_Limit is the name of the field containing that value. That way all you (or better yet the users) do is change the value in the table.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I agree with Paul. Hard coding values in queries is a PITA.

    I noticed you have hard coded a date to calc a persons age...... this means editing the query every time you want to execute the query.....

    Code:
    FROM ((Personal 
    LEFT JOIN Ben ON Personal.Name = Ben.Name)  ..........
    Be aware that "Name" is a reserved word in Access and shouldn't be used as object names.
    Also, having spaces and special characters ([Supp AD&D] ) is considered BAD.


    Object names should be letters and numbers.
    Do not begin an object name with a number.
    NO spaces, punctuation or special characters (exception is the underscore) in object names

  9. #9
    Coryjacques is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    16
    Thanks this is great feedback! I'm doing research on subbing out the date with something like Getdate() but I can't seem to swing it. any tips there?

  10. #10
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    The Access equivalent is Date() for the date part only or Now() for date and time.

  11. #11
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Looks like the query is in Access, which would mean you'd use Date().
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  12. #12
    Coryjacques is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    16
    Perfect, thanks everyone!

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

Similar Threads

  1. How do i use the IIF query for my equation.
    By pekimojoe in forum Queries
    Replies: 1
    Last Post: 05-08-2017, 10:11 PM
  2. Equation help
    By Thompyt in forum Programming
    Replies: 7
    Last Post: 05-15-2015, 05:31 PM
  3. still having equation problem
    By stryder09 in forum Access
    Replies: 16
    Last Post: 02-19-2011, 12:13 AM
  4. Another equation almost done now
    By stryder09 in forum Access
    Replies: 4
    Last Post: 02-18-2011, 12:18 PM
  5. Another equation question
    By stryder09 in forum Access
    Replies: 3
    Last Post: 02-17-2011, 11:41 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