Checking for Eligibility 1

[jackieflash]

This pertains to health insurance data.
I have two tables. One contains a members eligibility history (Eligibilty Table) and the other contains their visits to clinics, hospitals, etc ( Visits Table). I need to find a way to find if they are eligible for the visits.
The problem I am having is that some members could have more than one record for their eligibility. Thus for the member below there would be three records.
For eg. Member ID 123456 was eligible from :
Effective Date End Date
10/1/2000 6/1/2001
9/1/2001 12/1/2001
3/1/2002 present
Thus if member 123456 went to a hospital on 11/1/2000 and 7/1/2002 ( from the Visits table), how do I check against the eligibility table and see if he was eligible for that date of service.

My query only works when the member has only one eligibility record. How do I get it to search his other eligibility records to see if they match his dates of service.
The tables are joined by Member ID numbers.
I am trying to do this using QBE. If SQL would work better and anyboy has any suggestions, please let me know.

Thanks a lot,
Jackie Flash

 

[JeremyNYC]

Jackie,

Nice. I don't know if this is fully kosher, but it certainly seems to work on the small data set I made.

SELECT tblVisit.VisitID, tblVisit.PersonID, tblVisit.VisitDate, Max(IIf(([visitdate]>=[EffectiveDate]) And ([visitDate]<=[EndDate]),&quot;True&quot;,&quot;False&quot) AS Eligible
FROM tblVisit LEFT JOIN tblEffective ON tblVisit.PersonID = tblEffective.PersonID
GROUP BY tblVisit.VisitID, tblVisit.PersonID, tblVisit.VisitDate;

It uses an immediate if to check whether each visit is between the effective and end dates and it groups by visitID, and it looks at the Max of the result of the iif, because if it was eligible in one of the periods that's good enough for us.

Please test this on a larger dataset and let me know if it doesn't work. You'll of course want to go through and rename everything.

Hope this helps.

Jeremy =============
Jeremy Wallace

http://www.AlphaBetCityDataworks.com

Designing and building Access databases since 1995.

 

[jackieflash]

Hi Jeremy,
Thanks a lot for your input. Will see if the IIf works here. It will save me a lot of time.
Thanks again for your answer.

JackieFlash

 

[jackieflash]

Hi Jeremy,
Thanks for your query. It worked like a charm. However is it possible to add another condition to the IIF statement. Current members have their end date blank. That means that they are still active and eligible for services.
For eg,

Member ID Effective Date End Date VisitDate
123456 8/1/01 9/1/01
567890 6/1/01 9/1/02 8/1/01



Here member 123456 is active from 8/1/01 and has no end date. Since the visit date is 9/1/01, he is eligible for coverage. Member 567890 had his coverage end on 9/1/02.

So for member 123456 how can I check and see if they are eligible for services. I tried to use the IIF statement but it gave me an error. My other solution was to populate the blank fields in ENDDATE with the date 12/31/9999. This works but I would prefer not to change the table in any way.

Any help would be great.
Thanks,
JackieFlash