1 Reply Latest reply on Jul 1, 2011 9:39 AM by philmodjunk

    Doing Calculations Between Tables



      Doing Calculations Between Tables


      Hi!  I'm a newby so please excuse the basic nature of my question.

      I've created an accounting database for my business.  I have two separate tables or layouts:

      "Invoices" - each record is a separate invoice with a summary field in the footer totaling income from all invoices.

      "Expenses" - basically same as above with a summary field totaling all expenses.

      I'm trying to create a third table or layout that will show a calculation "profit = total income - total expenses" from the summary fields of the other two tables.  I know this should be simple, but I can't figure it out.

      Thanks for you help!


        • 1. Re: Doing Calculations Between Tables

          This requires a relationship linking the two tables and different relationships will produce different results. You'll need to figure out what kind of relationship will access the correct data for you. One way is to set his up with your third table linking to records in the other two by date:

          Say each record in your third table represents one Month's business. Let's call this third table "MonthlyTotals". Define a date field in it called "Month".

          Define a calculation field in the expenses and also in the invoices table, cMonth as: TransactionDate - Day ( TransactionDate ) + 1 with "date" specified as the result type. This calculation returns the date of the first day of the month for the given date. Use the name of your date field in place of TransactionDate.

          Now you can set up two relationships to MonthlyTotals:

          MonthlyTotals::Month = Invoices::cMonth
          MonthlyTotals::Month = Expenses::cMonth

          Now you can enter a date in Month for the first day of the month and your relationships will link to all records in Invoices and also in Expenses for that month. There are ways to automatically create one such record for each month of the year.

          Now a calculation field with this expression will produce your monthly profit: Sum (Invoices::InvoiceTotal) - Sum ( Expenses::ExpenseAmt )

          A summary field defined in MonthlyTotals can then compute the total of this calculation field to compute a total for the year, the quarter or anyother range of months that you need. You just create one MonthlyTotal record for each month and perform a find to pull up just the monthly totals that you want for your report.

          PS. this is just one of many possible ways to do this. If you linked your third table by a date field directly to transactionDate fields, you could do this with daily totals rather than monthly.