I have a Claims table with unique MembNo's and ClaimNo's and ClaimDates. I link this to a Member table with coresponding MembNo's. Members might have multiple coverage dates in the Member table, with fields for BegCovDate and EndCovDate, and a single record for each coverage. I need to find if a ClaimDate from Claims table falls within the range of BegCovDate and EndCovDate for ANY of the multiple coverages for a member. This query finds all claims that fall OUTSIDE a range:
SELECT CLAIM.MembNo, CLAIM.ClaimDate, CLAIM.ClaimNo MEM.BegCovDate, GOLDENMEM.EndCovDate
FROM CLAIM INNER JOIN MEM ON CLAIM.MembNo = MEM.MembNo
WHERE (((CLAIM.ClaimDate)not between [MEM].[begcovdate] and (CLAIM.ClaimDate)>[MEM].[endcovdate]));
How do I eliminate a Claim record if a ClaimDate falls within the range of ANY coverage (between BegCovDate and EndCovDate) for a particular MembNo?
In other words, for each member's claim, does the claim date fall within the coverage of ANY of the member's coverages -- I don't want just a listing of particular claims that fall out, but identification of a member who has a claim that does not fall within ANY of the ranges.
Is this some kind of correlated subquery? Is the answer using a VIEW statement in SQL (which I don't know how to do in Access)?
Thanks for any help.
SELECT CLAIM.MembNo, CLAIM.ClaimDate, CLAIM.ClaimNo MEM.BegCovDate, GOLDENMEM.EndCovDate
FROM CLAIM INNER JOIN MEM ON CLAIM.MembNo = MEM.MembNo
WHERE (((CLAIM.ClaimDate)not between [MEM].[begcovdate] and (CLAIM.ClaimDate)>[MEM].[endcovdate]));
How do I eliminate a Claim record if a ClaimDate falls within the range of ANY coverage (between BegCovDate and EndCovDate) for a particular MembNo?
In other words, for each member's claim, does the claim date fall within the coverage of ANY of the member's coverages -- I don't want just a listing of particular claims that fall out, but identification of a member who has a claim that does not fall within ANY of the ranges.
Is this some kind of correlated subquery? Is the answer using a VIEW statement in SQL (which I don't know how to do in Access)?
Thanks for any help.