Aren't expenses transactions? Why put expenses in a different table to begin with?
The fact that they are in separate table does complicate the process. It may still be able to chart them in a single chart if you used the delimited data data source option and use ExecuteSQL() to produce return separated lists of values to chart. One Y-series can produce a list of values from expenses and the other Y-series can be from transactions.
The transactions are income and the expenses are basically expenses, for tax purposes I need to keep them separate.
I'll have to have a look at the SQL, from a brief read it looks like the solution. Are there any references or tutorials on it for beginners.
But you can keep expenses and transactions separate and still have them in the same table. It would seem the simpler solution to keep them all in the same table and use data in an added field to distinguish expenses from income.
ExecuteSQL() is not for the faint of heart to use. The current implementation in FileMaker is not very easy to work with. One tiny syntax error and all you get is a ? result with no clue as to what error was responsible.
I can share a few resources for learning more on how to work with SQL:
Pull down FileMaker's help menu and select the ODBC JDBC guide from the product documentation sub menu. The SELECT query examples near the end of this document use the exact same syntax as the SQL that you use in the ExecuteSQL function.
Then there is a very helpful FileMaker file that you can download free from SeedCode called SQL Explorer that can help you set up your first SQL queries with this function.
And I have put together two small "how to's" using ExecuteSQL() that you might check out:
Thanks to both, I will do a bit of research, I don't mind learning something new.