Calculation in Non-Equijoin Relationship
Is it possible to have a field with a calculation joined to another table in a non-equijoin relatinship? Basically, here's my set up and problem...
Table 1 Receipts-for Students registered in a classs
Table 2 Enrollment-Used to resolve many-many relationship from Table 1-Table 3
Table 3 Classes-Class information, including class start date
Table 1 and 2 are joined by StudentID
Table 2 and 3 are joined by ClassID
I have a date range on Table 1 Receipt and want it to pull the class information if the student is enrolled in a class starts within the date range of the receipt. I thought this could be resolved by having a field in Table 2 of "ProgramStartDate=Table3:ClassStartDate" and then join tables 1 and 2 by "ProgramStartDate >= DateRangeStart" and "ProgramStartDate <= DateRangeEnd". Sadly this isn't the case...
I do have a portal set up in Table 1 to pull class information which works until I make the relationships for the date range. Any ideas?