6 Replies Latest reply on Jun 13, 2012 1:56 PM by SlicChic

    Calculation in Non-Equijoin Relationship

    SlicChic

      Title

      Calculation in Non-Equijoin Relationship

      Post

      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?  

        • 1. Re: Calculation in Non-Equijoin Relationship
          philmodjunk

          Receipts---<Enrollment>----Classes

          Receipts::StudentID = Enrollment::StudentID
          Classes::ClassID = Enrollment::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 think you are trying to use one table for two different things. Receipts (billing info) and Students ( contact info ).

          I'd modify your set up by adding a new table just for the reciepts to get this:

          Students----<Receipts---<Enrollment>----Classes

          Students::StudentID = Receipts::StudentID
          Classes::ClassID = Enrollment::ClassID

          The relationship for receipts to Enrollment might become:

          Receipts::StudentID = Enrollment::StudentID AND
          Receipts::DateRangeStart <= Enrollment::ProgramStartDate AND
          Receipts::DateRangeEnd >= Enrollment::ProgramStartDate

          But you'd need to use auto-enter or Looked Up Value field options to copy the program start date from classes into Enrollment instead of using a calculation.

          An alternative approach would be to use this relationship:

          Receipts::ReceiptID = Enrollment::ReceiptID

          Which allows you to set up a receipt record with a portal to Enrollment that lists all enrollment records billed on that receipt--independent of the start date..

          • 2. Re: Calculation in Non-Equijoin Relationship
            SlicChic

            Thanks.  I actually do have the whole database as you've suggested, with Students in a separate table, joined to receipts.

            So, I've been trying to get the data using the Looked-Up Value field options.  I've uploaded a screen shot of the options I'm selecting.  I have tried to relookup the values, and nothing is entered into the field.  Is there a step I'm missing here?  Or a relationship not quite right?

            • 3. Re: Calculation in Non-Equijoin Relationship
              philmodjunk

              First check to see if a new record correctly looks up the date. If it does, you haven't got your Relookup quite right to update for your existing records. If it doesn't, you'll need to examine the relationship between reciepts and classes and also the looked up value options you specified.

              Your screen shot did not appear, please make sure that you upload a file with one of the three file types that are permitted for such an upload.

              • 4. Re: Calculation in Non-Equijoin Relationship
                SlicChic

                It does correctly look up the date for a new record, but its as you said, the Relookup is quite working properly.  Ist there someway to fix that?  

                Sorry about the last screenshot...forgot to look at the file type and then had to leave for a moment...here's the image.

                • 5. Re: Calculation in Non-Equijoin Relationship
                  philmodjunk

                  The trick to relookup is to put the focus in one of the key fields that define the relationship--not the ProgramStartDate field. Show All records on an Enrollment layout, put the cursor in one of the fields that are used to define the relationship and then select Relookup.

                  • 6. Re: Calculation in Non-Equijoin Relationship
                    SlicChic

                    Perfect!  That worked.  Thanks!