Page 1 of 2 12 LastLast
Results 1 to 15 of 28
  1. #1
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Lightbulb IIf statement for member eligibility

    Here is my IIf statement right now (which works, just not as sophisticated as I need it to be):

    Eligibility: IIf(([DATEFROM]<[dbo_RVS_MEMB_HPHISTS].[OPFROMDT]) Or ([DATEFROM]>[dbo_RVS_MEMB_HPHISTS].[OPTHRUDT]),"Not Eligible","Eligible")

    I am determining Member\Patient Eligibility and the thing here is that the members have Eligibility Sequences\Histories.

    For example, let's say, I joined a Health Plan called IEHP on 1/1/09 and ended with IEHP on 12/31/09 and then I joined a different Health Plan called Molina on 3/1/10 and then I ended with them on 2/28/11 (and so on and so forth....). Now, let's say I have any number of Dates of Services (e.g., I caught the flu and was seen by my doctor on 4/22/09, I got a nail in my foot and had to go to Urgent Care on 2/14/10, etc., etc.).

    As you can probably see, my flu DOS on 4/22/09 would be covered because I was eligible at the time (IEHP 1/1/09 through 12/31/09).
    As you can probably also see, my nail in my foot on 2/14/10 would not be covered because I was not enrolled with a Health Plan at the time of my Date of Service (DOS) and therefore "Not Eligible".

    So the tricky thing here is that my Eligibility IIf statement is working like this following screenshot (DATEFROM is the Date of Service):

    MembEligibility.PNG

    So I think this example explains it best. So basically, if any of the Member's Eligibility Sequences covers the Date of Service, then I need Eligibility column to show as Eligible. I am planning to export the query results to Excel spreadsheet and then filter for "Not Eligible" (so we can perform Recovery processes).

    I am thinking that this might possibly work:

    I created a Step 2 query (bringing in * from Step 1 query as explained above) and added another field called Eligibility with a subsequent IIf statement as follows: Eligibility2: IIf(([CURRHIST]='C') Or ([CURRHIST]='H') And ([Eligibility]='Eligible'),"Eligible","Not Eligible")

    That just results in the following screenshot:

    MembEligibility2.PNG

    Thank you in advance to anyone who can help me.
    Last edited by LukeB; 2013-08-05 at 15:10.

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    You say:
    As you can probably also see, my nail in my foot on 2/14/10 would not be covered because I was not enrolled with a Health Plan at the time of my Date of Service (DOS) and therefore "Not Eligible".
    But you were enrolled in Molina between 3/1/10 and ended on 2/28/11, isn't 14th Feb 2010 between 1st March 2010 and 28th Feb 2011?


    I don't understand what you wish to do here.

  3. #3
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    That is exactly the point.

    There shows 2 records as you can see in the screenshot (if I export results to spreadsheet, I would want to filter for only Not Eligibles so that we can RECOVER the payments we made to Providers, but if every Date of Service is duplicating to correspond with each member's history of eligibility, multiple records appear for each member).

    If member was eligible as of Date of Service, there is no need to show them as Not Eligible for their other eligibility history sequences.

    The results of the screenshots shown in post #1 are technically correct, but not going to work for the next step of identifying which payments we need to RECOVER from Providers.

    2/14/10 isn't between 3/1/10 and 2/28/11 by the way (??)

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    You are quite right Luke, I made a mistake on the dates, in Australia we are used to d/m/yy but you are showing them as m/d/yy.
    You say the queries in post #1 are technically correct, what is the criteria for the next step to work?

  5. #5
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    No need to worry about that (how interesting about the whole d/m/yy thing....so foreign to me)

    I am going to create a small little simple database so you or anyone else can please help me with this and I am going to try to get that together today.

    Thank you in advance for all you do.

    And attached is the database sample I am talking about.

    I think I would prefer to have the Eligibility column say Eligible if any of the DATEFROM (Date of Service) dates for a particular member are truly Eligible from any of his/her other Member Eligibility Histories\Sequences (so that way when I export it to Excel, I could just filter out the "Not Eligible" list and that will be our list to RECOVER payments made to Providers).

    I hope this helps and let me know of any questions.
    Attached Files Attached Files
    Last edited by LukeB; 2013-08-13 at 12:19.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    I notice there are duplicates returned in your query, is this correct?

    What do you want returned by the query?

  7. #7
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    The duplicates are what the issue is (duplicates are caused by Member's Eligibility History Sequences).

    If you run the query in the sample DB, and check out the first 5 records (Name: One, Person -- DATEFROM: (All) 5/23/2009 -- OPFROMDT: (All different due to the different Histories\Sequences). Now notice that the first row having DATEFROM of 5/23/2009 falls between the History\Sequence for One, Person of 1/1/2009 to 12/31/2009 which is why the IIf statement is showing that record as "Eligible". Now notice the next 4 records where DATEFROM are still 5/23/2009 and the History Sequences are different and the IIf statement is determining that the same Member is "Not Eligible".

    The problem here is that if one of the Historical Sequences is "Eligible" (since it truly is), the other Historical Sequences is\are "Not Eligible". What I need is it to show all records where the Member is the same Member (MEMBID), the DATEFROM is the same (DATEFROM), and the OPFROMDT and OPTHRUDT are all different Sequences as "Eligible". In other words, if the Date of Service (DATEFROM) is covered by 1 of their Historical Sequences (or Current History), then the other Historical Sequences (and Current History) should also show as "Eligible" (for each particular DATEFROM that are the same for the same Member).

    This is because I plan to export results to Excel spreadsheet and then filter for only "Not Eligibles" so that we can RECOVER payments we shouldn't have paid to Providers.

    I hope this clears up any confusion? Let me know please and Thank you for your help.

  8. #8
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Have you tried to solve this with a subquery?
    Here is a reasonable link to show how that might work
    http://allenbrowne.com/subquery-01.html
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  9. #9
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  10. #10
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I will check out those links after I get a chance to breathe from my other projects....did you happen to play around with my sample DB in post #5??

    Let me post my latest modifications (not sure if that one has everything the same)....
    Attached Files Attached Files

  11. #11
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    Diod yiou see my eample in your other related post HERE
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  12. #12
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Not yet, it is really killing me that I have to first complete another tedious project right now (I will as soon as possible). Thank you so much!!

  13. #13
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    So I just checked out that sample DB you uploaded and that doesn't solve this problem....The Claims Recovery Paid_Summary_ProvName_Step1_HiTechCoach only pulls in the first DATEFROM (Date of Service) for a given member (members are seen for health issues more than once all the time) and the qryClaimsEligible only pulls in DATEFROM Between [OPFROMDT] And [OPTHRUDT] (What about the Claims where the DATEFROM is outside of the Member's History date range?).

    Did you look at post #10, my DB Sample 3?

    If you set the Q_test Memb Eligibility Step 1 last column to True or False, it will return what I need and what I don't need (meaning it will duplicate "Eligible" and "Not Eligible" for all DATEFROMs (Dates of Service) and for all OPFROMDT and OPTHRUDT (their history of eligibility). So I would have a block of "Eligible" for all and a block of "Not Eligible" for all.....

  14. #14
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,852
    Thanks
    4
    Thanked 259 Times in 239 Posts
    Try this.
    Create a VBA module in the database and add this function
    Code:
    Function CoverStatus(lngMembID As Long, dteEvent As Date) As String
      If DCount("*", "dbo_RVS_MEMB_HPHISTS", "#" & dteEvent & "# between [OPFROMDT] and [OPTHRUDT]") > 0 Then
        CoverStatus = "Eligible"
      Else
        CoverStatus = "Not Eligible"
      End If
    End Function
    Now create a query that calls this function. I modified the final field in the query you supplied to become the following
    Code:
    SELECT dbo_RVS_CLAIM_MASTERS.MEMBID, dbo_RVS_CLAIM_MASTERS.MEMBNAME, dbo_RVS_CLAIM_MASTERS.CLAIMNO, dbo_RVS_CLAIM_MASTERS.PROVID, dbo_RVS_CLAIM_MASTERS.VENDOR, dbo_RV_VEND_MASTERS.VENDORNM, dbo_RVS_CLAIM_MASTERS.DATEPAID, dbo_RVS_CLAIM_MASTERS.STATUS, dbo_RVS_CLAIM_MASTERS.BILLED AS Billed, dbo_RVS_CLAIM_MASTERS.CONTRVAL AS ContrVal, dbo_RVS_CLAIM_MASTERS.NET AS Net, dbo_RVS_CLAIM_MASTERS.DATEFROM, dbo_RVS_CLAIM_MASTERS.DATETO, dbo_RVS_CLAIM_MASTERS.CHECKNO, dbo_RVS_CLAIM_MEMOFLDS.MEMOLINE4, dbo_RV_VEND_MASTERS.TAXID, dbo_RVS_AUTH_MASTERS.AUTHNO, dbo_RVS_CLAIM_MASTERS.PLACESVC, dbo_RVS_MEMB_HPHISTS.OPFROMDT, dbo_RVS_MEMB_HPHISTS.OPTHRUDT, dbo_RVS_MEMB_HPHISTS.CURRHIST, CoverStatus([dbo_RVS_CLAIM_MASTERS].[MEMB_KEYID],[dbo_RVS_CLAIM_MASTERS].[Datefrom]) AS Eligibility
    FROM ((((dbo_RVS_MEMB_COMPANY INNER JOIN dbo_RVS_CLAIM_MASTERS ON dbo_RVS_MEMB_COMPANY.MEMB_KEYID = dbo_RVS_CLAIM_MASTERS.MEMB_KEYID) LEFT JOIN dbo_RVS_AUTH_MASTERS ON dbo_RVS_CLAIM_MASTERS.AUTHNO = dbo_RVS_AUTH_MASTERS.AUTHNO) INNER JOIN dbo_RVS_MEMB_HPHISTS ON dbo_RVS_MEMB_COMPANY.MEMB_KEYID = dbo_RVS_MEMB_HPHISTS.MEMB_KEYID) LEFT JOIN dbo_RVS_CLAIM_MEMOFLDS ON dbo_RVS_CLAIM_MASTERS.CLAIMNO = dbo_RVS_CLAIM_MEMOFLDS.CLAIMNO) INNER JOIN dbo_RV_VEND_MASTERS ON dbo_RVS_CLAIM_MASTERS.VENDOR = dbo_RV_VEND_MASTERS.VENDORID
    WHERE (((dbo_RVS_CLAIM_MASTERS.DATEPAID) Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)) AND ((dbo_RVS_CLAIM_MASTERS.STATUS)="9") AND ((dbo_RVS_CLAIM_MASTERS.NET)<>0) AND ((dbo_RVS_CLAIM_MEMOFLDS.MEMOLINE4) Not Like "*RC*" And (dbo_RVS_CLAIM_MEMOFLDS.MEMOLINE4) Not Like "*R$*" And (dbo_RVS_CLAIM_MEMOFLDS.MEMOLINE4) Not Like "*Refund*") AND ((dbo_RV_VEND_MASTERS.TAXID) In ("9999","8008","3702")));
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  15. #15
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    I basically did the same thing Andrew Lockton did but I used a query not a VBA function.

    in the 30+ years I have been doing medical billing and claims filling I have always looked to see on a "by claim" basic it the date of service had any plan coverage. That is my in the example I post it was calculating if a claim was covered. The query would return a list of all covered claims. You can use the query to find all the covered claimes by the ones that matched. The claims not covered would be a "no match".

    From what you have described is sounds like you want to figure out is a single claim is "eligible" (covered). Is that correct? If not, please explain in plain English (not programming/query/sql/vba terms) what you need.
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

Page 1 of 2 12 LastLast

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
  •