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 date 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

 

[mymou]

Hi jackieflash

Making lots of auusumtions, including that you also have another table storing 'member' information:

qryValidDates:

SELECT tblMembers.MemberName, tblEligibilty.DateStart, tblEligibilty.DateEnd, tblVisit.VisitDate
FROM (tblMembers INNER JOIN tblEligibilty ON tblMembers.MemberID = tblEligibilty.MemberID) INNER JOIN tblVisit ON tblMembers.MemberID = tblVisit.MemberID
WHERE (((tblEligibilty.DateStart)<[VisitDate]) AND ((tblEligibilty.DateEnd)>[VisitDate]));

The tblMembers is redundant - but does show the situation a little clear I think.

Having only done this quickly - but I think this pulls all 'valid' dates - so in 'invalid' dates should be easy enough to find.

Stew

 

[jackieflash]

Hi Stew,
Thanks for the reply . Appreciate it. However it doesnt work. There are only two tables, (1)Eligibility Table AND (2) Visits table. The Eligibility table has all the member information in it including member ID.

Eligibility Table

Member ID Effective Date End Date
123456 10/1/2000 6/1/2001
123456 9/1/2001 12/1/2001
123456 3/1/2002 present

As you can see member ID has three entries. However his coverage is not continuous, there are breaks between 6/1/2001 and 9/1/2001 etc. This causes me the problems

Visits Table

Member ID Dates of Service
123456 10/15/2001
123456 11/1/2000

How do I check if these dates of service fall between the period they were covered.
LEt me know if you can think of something else.
Thanks,
JackieFlash

 

[mymou]

Hi jackieflash

As I said - the tblMembers is redundant - so is unnecessary in the query - just makes thing clearer.

SELECT tblEligibilty.DateStart, tblEligibilty.DateEnd, tblVisit.VisitDate
FROM tblVisit INNER JOIN tblEligibilty ON tblVisit.MemberID = tblEligibilty.MemberID
WHERE (((tblEligibilty.DateStart)<[VisitDate]) AND ((tblEligibilty.DateEnd)>[VisitDate]));

This should do the same thing - I think.

You should have a separate table. Your db is inefficient without it - and must make form design more dificult. For MemberID 123456 you have 3 entries of member name etc, when you only really need one. To display this on a form efficiently is difficult. If you had a separate table, you would be able to use subforms very easily.

Stew

 

[jackieflash]

Stew,
Thanks a lot for the feedback.Will take your advice to create a separate table for members.

Thanks,
JAckieFlash