Many-to-many query looking for aggregate condition

[dfkehn]

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.

 

[tsumrall]

I made a small data set like you have and this seemed to work. Not sure what the goldenmem was so I left that out.
A claim may show twice because it doesn't fall in 2 different coverage periods. Am I missing something?

SELECT [CLAIM].[membno], [CLAIM].[claimdate], [CLAIM].[claimno], [MEM].[begcovdate], [MEM].[EndCovDate]
FROM CLAIM LEFT JOIN MEM ON [CLAIM].[membno]=[MEM].[membno]
WHERE ((([CLAIM].[claimdate]) Not Between [MEM].[begcovdate] And [MEM].[endcovdate]));

 

[dfkehn]

Thanks Tsumrall. Your solution was what I needed to identify claims that did NOT fall within SOME coverage, but that member might have other coverage for that claim. Say I have a member with 3 coverages, 1/1/01 thru 1/31/01, 3/1/01 thru 3/31/01, and 5/1/01 thru 5/31/01. A claim from 5/11/01 would show two non-coverages and one coverage met. How can I capture that one coverage STILL RELATED to the two non-coverages? I.e., how do I identify that a single claim is associated with one of any coverages? I need to compare a MembNo/ClaimNo to all coverages for the MembNo, and if ANY coverage is met, then ALL records for that MembNo/ClaimNo is rejected, not just the single instance where the coverage is met. Or in other words, how to compare a record from Claims table to all records in MembNo, and if the ClaimDate falls within the BegCovDate/EndCovDate of ANY record from MembNo, then the original record passes. Is that confusing enough?