Results 1 to 6 of 6
  1. #1
    sergi117 is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    30

    Choosing latest criteria amongst choices

    Attempting to create sum(iif
    Code:
     Sum(IIf([effective date]<=[pp end] And [Action]="HIR" or [action]="REH",1,0))
    The idea is it is counting the amount of Pay periods where the effective date is less than/ equal to. And I only want this done for either if it is action "HIR" or "REH". What I'm trying to do is if there is an action HIR and REH or multiple REH, the sum iif should only apply to the action with the latest effective date. I was considering a max within the iif function, but I'm unsure how to do so.



    The Table is essentially
    Empl_ID....Effective_Date...Action...(My count/ sum(iif))

    See the image example to get an idea.Click image for larger version. 

Name:	Capture7.PNG 
Views:	17 
Size:	8.3 KB 
ID:	35761

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Cross-posted https://stackoverflow.com/questions/...ed-on-category

    Post an example of desired output. Mockup in Excel if you want, or build a table in the post with the Advanced editor.
    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.

  3. #3
    sergi117 is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    30

    Unrelated link

    Quote Originally Posted by June7 View Post
    Cross-posted https://stackoverflow.com/questions/...ed-on-category

    Post an example of desired output. Mockup in Excel if you want, or build a table in the post with the Advanced editor.
    The link you posted is related to counting the number of dates within a range, stopping when effective dates for LOA fit into range and continuing after LOA no longer fits.

    The result I'm looking for is similar to the excel attachment. Please see image Click image for larger version. 

Name:	example.PNG 
Views:	16 
Size:	13.4 KB 
ID:	35763
    Attached Files Attached Files
    Last edited by sergi117; 10-08-2018 at 06:43 PM.

  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
    Zip folder is empty.

    What is purpose of Result field? So the value is either 0 or 1, what do you intend to use this for? Do you really need to show all the records?

    If you just want the latest Effective Date record for each employee, use TOP N query (http://allenbrowne.com/subquery-01.html#TopN) to retrieve. Filter out records that don't have HIR or REH
    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
    sergi117 is offline Advanced Beginner
    Windows 10 Access 2013 32bit
    Join Date
    Sep 2018
    Posts
    30
    The bigger idea is to then check based on a table with ranges of dates (beginnings and ends) to count how many ranges the effective date of the latest REH or HIR fits into. The other records should stay since, I'm later planning to subtract counts for when the action is something else specific. Though if need be I may have to make multiple separate queries for the relative actions. Are there not any forms of top N for writting in an iif statement? If not, I'm thankful for the assistance and I'll work through it with a select top 1 query.

    The key factor is between the 2 actions, picking the one with the latest date which normally would be REH if it is there, but if REH isn't there then HIR.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Well, been looking at this one for a bit and don't quite get it.
    First, don't think you can use a TOP predicate unless you can sort the data ascending or descending, which it doesn't appear that you can here.
    Second, non-grouped logical operators often don't work as expected. Let's remove some of the parentheses to make it more obvious and add others to make a point, then consider which of these you really mean:

    ([effective date]<=[pp end] And [Action]="HIR") or ([action]="REH",1,0)

    ([effective date]<=[pp end]) And ([Action]="HIR" or [action]="REH",1,0)

    Your expression can evaluate to

    IIF(True OR False) when the first comparison is True, but also
    IIF(False OR True) when the first is False and the 2nd is True. Either way, the IIF evaluates to TRUE, which is probably no good to you.

    I don't think you've provided enough data or enough result examples, and as June7 mentioned, the zip folder is empty. Perhaps that would have provided some of the missing stuff, like [pp end] and Result.

    Your expression is probably part of a sql statement, in which case a UNION query comes to mind if you can't resolve the expression problem. In other words, you'd get records for an applied date for each pertinent action, then query the max based on those results. I'm not yet convinced you have to resort to that though.
    Last edited by Micron; 10-08-2018 at 08:28 PM. Reason: correction
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. choosing language
    By MykeRuiz in forum Programming
    Replies: 1
    Last Post: 06-27-2016, 09:14 AM
  2. Replies: 3
    Last Post: 01-06-2016, 07:42 PM
  3. Choosing Criteria based on option Box Value
    By todmac in forum Queries
    Replies: 3
    Last Post: 10-02-2015, 11:51 AM
  4. Choosing Report
    By ShadeRF in forum Reports
    Replies: 12
    Last Post: 11-18-2011, 11:31 AM
  5. help in choosing from list
    By Nokia N93 in forum Programming
    Replies: 4
    Last Post: 11-30-2010, 07:29 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