Results 1 to 15 of 15
  1. #1
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Member Eligibility Formula Needed??

    I have a spreadsheet I exported from MS Access that shows a column called Eligibility (from my IIf statement in MS Access).

    Problem is that members have multiple eligibility periods in a History table used in my query so the results show the same Date of Service for 1 Member but 2 Eligibility Sequences (From Date and Through Date has sequential periods. example: 1/1/09 through 12/31/09 is one sequence and 4/1/11 through 3/1/13 is another sequence).

    So my Eligibility IIf statement in Access is this (works exactly how I want it to except it works for multiple records of the same member):

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

    So even if the Date of Service falls within the Eligibility period of a member, the other sequence(s)\period(s) don't fall in that time range which is making the value of the Eligibility column for those records Not Eligible.

    What I need I think is a formula to say if the MemberID (another column) and Date of Service are the same exactly whose record shows as correctly "Eligible", make the other cells in the Eligibility column say "Eligible", too, for that Member.

    Please help.....googling and asking everyone to no avail so far.....

    Running out of ideas?

    I was trying to do something with the formula And (like this: =And(A2=A3, Q2=Q3) and then make that inside of another formula where column A is MemberID and column Q is Date of Service)?? So if the results of the =And formula return True and the Eligibility column for a particular record says "Eligible", make the next one "Eligible" too and if that particular record says "Not Eligible", make the next one "Not Eligible", too. I hope this is making sense??
    Last edited by LukeB; 2013-08-05 at 18:53.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Can you post an example workbook demonstrating some of the issues? It is difficult to post an example without a structure.

    Steve

  3. #3
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Will do first thing tomorrow Steve (sorry got to go to Dental appointment now) for sure (I was hoping someone would ask for a sample)...
    Last edited by LukeB; 2013-08-13 at 16:27.

  4. #4
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you in advance for all you do.

    Attached is a database sample.

    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

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    Hi Luke

    Attach the sample excel file you have, when you get time.

    zeddy

  6. #6
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I will do that hopefully soon, then I can give that one to the Recovery person.

    But I really would like to get it to work in Access (my sample database is in post#4 above), just struggling on the how part?

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    If you want/need access help, you should probably ask to move this thread to the access forum.

    Steve

  8. #8
    Lounger
    Join Date
    Jul 2013
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    So attached is my spreadsheet I exported from Access sample DB I created with same logic and structure as real DB.

    I want to be able to handle it in Access, YES, so I guess this thread should move to access forum, but I want it to work as soon as possible so maybe from uploading my attachment, I can get a solution quicker (then ask to move to access forum, right?)
    Attached Files Attached Files

  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
    If you want to handle this with Access, it would be helpful if you posted an example database not an Excel file.

    You should be able to do what you want within a query in Access. I would use a sub query or a second query to calculate the eligibility for the member.
    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
    Here you go.
    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
    I just saw this this Other Post Here of yours.. Is this the same issue?
    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
    Yes

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

  14. #14
    3 Star Lounger HiTechCoach's Avatar
    Join Date
    Sep 2011
    Location
    Oklahoma City, OK
    Posts
    200
    Thanks
    0
    Thanked 31 Times in 30 Posts
    See if the attached is what you want.

    MemberEligibilityHistorySampleDatabase_HiTechCoach.zip
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

  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
    Boyd Trimmell aka HiTechCoach (Access Information here)
    Microsoft MVP - Access Expert
    "If technology doesn't work for people, then it doesn't work."

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
  •