5 Replies Latest reply on Jan 29, 2014 9:13 AM by philmodjunk

    To Add Previous Balance

    JavedulHaq

      Title

      To Add Previous Balance

      Post

           Hi, Here is a simple db of a ice cream company. I would like to explain it first.

           Malai, Fruity, dip etc. are Items name.

           Quantity = Number of items

           Return = Number of items which are not sold.

           Balance = Quantity - Balance

           Retail Price = Rates

           Sub Total = Balance * Retail Price for each item.

           and .............................

           Sub Sale = Sum of Sub Total

           Cash/Adv = Amount salesman takes from company for any need.

           Current Sale = Sub Sale + Cash/Adv

      Prev. Balance = Balance of previous day ( Last Rec of same salesman). Here is the problem.

           Total Sale = Current Sale + Prev. Balance

           Paid Amount = Amount paid by sm.

           and Balance = Total Sale - Paid Amount.

           Every thing is fine except "Prev. Balance because I couldn't add it automatically. I go to last Rec. gets Balance ant enter here. What i want is that Prev. Balance should be entered automatically. I also used summary of Balance but it gives value of all records. Please help me.

      Project1.jpg

        • 1. Re: To Add Previous Balance
          JavedulHaq

               This is Relationships.

          • 2. Re: To Add Previous Balance
            JavedulHaq

                 Can anybody reply me? Any hint plz?

            • 3. Re: To Add Previous Balance
              philmodjunk

                   on which of these three tables is your layout based?

                   Do you have a field for identifying each salesman?

              • 4. Re: To Add Previous Balance
                JavedulHaq

                     "Sale Sheet" is based on "tblSale". Each salesman is identified in "tblSmNames".  All + - are in "tblBalance". And In "LaySale Sheet" the field "Name" is a dropdown list (Two fields tblBalance: :tblSmNamesIdFk and tblSmNames: :Name)

                • 5. Re: To Add Previous Balance
                  philmodjunk

                       Select Balance while in the window shown in your screen shot and then click the duplicate button (two green plus signs) to make a duplicate table occurrence.

                       Link it to Balance like this: (You can double click the new table occurrence to open a dialog where you can rename it as I have done here.)

                       Balance::SmNamesIDFk = Balance|SameSalesMan::SmNamesIDFk AND
                       Balance::Date > Balance|SameSalesMan::Date

                       Double click the relstionship line and specify a sort order for Balance|SameSalesMan that sorts the related records in descending order by Date. This makes the next oldest record for this salesman the first related record when you use this new relationship.

                       You can then set up a field in Balance that uses a looked up value setting to auto-enter the desired value from Balance|SameSalesMan to use as the preceding balance in your calculations.