multiple results on a single record to match to

[macit]

This may have been covered, I'm new to this forum.

I want a record in File B to match to a record in File A if "07/2007" from B matches a record in File A whose start and end dates touch (begin, occur during, or end) in 07/2007.

It occurs to me that you can match to a (dreaded!) repeating field, if only I could figure out how to populate that field with a month/year with one repetition value for each month.

My guess is that there's a smarter way to handle this without resorting to a repeating field, but how do you match to a bunch of possibilities?

In case this isn't clear enough, say a class starts on May 15th and ends on July 3rd. I need to pull a report for June that would include this class. In July I need to pull the report and it should include this class with the new info (drop outs, collected tuition, etc), and in Aug when I pull this report, this class should not appear at all.

Thanks

[Fenton]

I think you should write year and month as: 200707, a number result.* That makes it a lot easier to sort, or do greater than and less than. Then it's just a matter of doing a compound relationship with greater than or equal to start, less than or equal to end.

Year ( date ) & Right ( "0" & Month ( date ); 2)

Number result. Yes, you could be more explicit, but it works fine, use it often.

Edited August 8, 2007 by Guest