5 Replies Latest reply on Apr 30, 2011 7:17 AM by user14405

    Can a field be set to retrieve the previous balance?

    bwbono

      Title

      Can a field be set to retrieve the previous balance?

      Post

      I have a small school.

      30 students each pay monthly for a year.

      I would like to have a field that will show the previous month's balance.

       

      For instance...if last month, a student paid less than their scheduled amount, I would like to show this on the next record, as part of the record.

       

      So, this new month, there would be the field showing the previous month's balance.

       

      Seems easy, but I just can't get it. Any help is greatly appreciated. 

        • 1. Re: Can a field be set to retrieve the previous balance?
          philmodjunk
            

          You can probably set up a self join that refers to the current students record but one month previous.

           

          I am assuming one record per student per month and that two fields already exist in your table:

          StudentID (Unique Serial Number)

          TransDate (Date of record's entry)

           

          TansDate should be set to auto-enter a date with a consistant "day" value.

          One such option: Date ( Month (self) ; 1 ; Year (self) ) with the "do not replace existing value" option cleared. (This assumes you enter the date manually each time.)

           

          Define a new field, cPrevMonth:

           

          Date ( Month ( TransDate ) - 1 ; Day ( TransDate ) ; Year ( TransDate ) )

           

          Create a new table occurrence, PreviousMonth for your student table. and define your relationship

           

          StudentTable :: cPrevMonth = PreviousMonth :: TransDate AND

          StudentTable :: StudentID = PreviousMonth :: StudentID

           

          Now you can refer to the fields in the PreviousMonth table Occurrence in any calculations or simply add the balance field from PreviousMonth on your layout.

          • 2. Re: Can a field be set to retrieve the previous balance?
            bwbono
              

            Phil, you are absolutely great!

            I will try this right away. Thank you very much for sharing your time and mind. Very cool of you. 

            • 3. Re: Can a field be set to retrieve the previous balance?
              bwbono
                

              I'm confused...is this a self-join?

               

              I made the new table, but I cannot get it to show previous balance.

               

              During a month, any student can pay more than once. AND the payment info comes from another table that collects the finances. These are from the bank and the transaction  date is always moving. So, maybe this is why I cannot get it to work?

               

              Could there be another way to relate these tables that can show the last month's balance?

               

              Thanks again for your time and knowledge and most of all, your effort and willingness to be so helpful. 

              • 4. Re: Can a field be set to retrieve the previous balance?
                philmodjunk
                  

                "I'm confused...is this a self-join?"

                Yes, I described a self join. If you have a calculation field that extracts the current month's total payment, then you can use the self join to refer to last month's total payment.

                 

                To extract the total payment from your payment table, do the following:

                 

                Define a cMonth field: Date (Month (transactiondatefield) ; 1 ; year (transactiondatefield) )

                 

                Now all payments in a given month will show the same date in cMonth.

                 

                You can use this relationship for the current month's total payment:

                StudentTable :: TransDate = paymenttable :: cMonth AND

                StudentTable :: StudentID = paymenttable :: StudentID

                 

                and this relationship for the previous month's total payment:

                StudentTable :: cPrevMonth = PaymentTable :: cMonth AND

                StudentTable :: StudentID = PaymentTable :: StudentID

                 

                • 5. Re: Can a field be set to retrieve the previous balance?
                  user14405

                  Thank you for the answer on this one. I was stuck trying to get previous record values to show up in new records, and this was the answer. It took some playing around with the relationship diagram, but I got it. Thanks again.