Join and summarize data found in several tables

The best way to summarize data from multiple tables based on a date (month) is to create a "Months" table, and depending on the details that you want it could be good to create a "Days" table.

I'll explain the "Days" table, as I think this will help you best.  Then you can always roll up things to weeks/months/years summary reports.  
(If you get stuck or have other questions, post back, because this is considered an advanced technique)

Create a "Days" table.

Create one field called "Date".

Set the 'Date' field as the Key field on the table.

Use excel to pre-load all the records you might need.
     i.e.  01-01-2014 thru 01-01-2025

Then create a relationship between all of those tables and the "Days" table.

Each Day will have many Time/Exp/Materials/etc.

Once the reference field is made on the child tables, it should be a 'Reference Date' field.

Then you can change that field properties to be a 'Formula - Date' field.
Then insert a formula that references the [Expense Date] field.

Now you will have a 'formula relationship' that will automatically connect all those records to the respective dates.

Create all the summaries you need. i.e. Total Amount - Time, Total Amount - Expense, etc.

Thanks For the Reply. I can see how that will roll-up all of the amounts based on date but I ma not sure how it will be filtered on cost Codes and Projects. I don't just need an over all total but a total specific to a cost code and then project for each month.

Created with Lucid Chart (lucidchart.com)



QuickBase does not have a general purpose query language such as SQL. Queries in QuickBase are restricted to queries of a single table and fields that can be "looked up" from parent or grandparent tables. So you could query the Expenses table and include fields from the Cost Codes and Projects tables that are related through lookup fields. But you could not simultaneously include fields from the Time Tickets or Materials table because those tables are not parents of the Expenses table. However, if you summarized some of the Time Ticket or Materials fields to the Cost Codes table those summarized fields could be included as lookup fields of a query of the Expenses table.

However, using script and the QuickBase HTTP API you can often generate a set of data that represent a more complicated query and the use HTML templating (eg Mustcahe) to render an appropriate report of the results. When you take this approach you generally use a library such as underscore.js or linq.js (a JavaScript implementation of Microsoft's LINQ - Language Integrated Query) to manipulate the raw data obtained from API calls. This process is essentially a manual implementation of the "query plan" that tools like SQL perform automatically.

The best way to proceed with a solution is to come up with (1) a sample set of data for each table and (2) a representative report format you want to see come out.