Simple use case. Personal finance management. Single table that has a budget transaction for each expense item (eg rent, netflix, car rego).
At the start of each month a new set of budget transactions is generated for the month (Payee, date due, amount due, payment status etc)
When an expense is paid, the transaction is updated from “Not Paid” to “Paid” together with Date paid.
There is a need to be able to calculate and show the total unpaid and paid by month, at any point in time.
The way I am doing this is by using 4 columns per month to maintain a summary of the payment state of transactions - one column per month to record amounts paid, one column per month to record amounts unpaid and then a “rollup” column for each of these two columns.
My question is …how is the best way to provision the summary table columns? - in the main table with all the transaction data? …or do I create a “mirror” table (in which all the summary columns are stored) and connecting it to the main table using a 1-1 relationship.
The disadvantage of the latter method is the work required to keep the two tables “in sync” - think add, edit/update, delete to the main table will require actions to be created to reflect the changes in the mirror table.
It feels a little counter intuitive / major “work around”, to add 48 columns to the main data table…to maintain the up-to-date paid/unpaid status for each of the 12 months, when that summary could feasibly be calculated dynamically (based upon the existing data in the table) at the time of a query.
If you are adding columns for each month instead of adding rows, I think you need to reconsider your data structure. Your database should be set up in a way where you rarely need to repeat columns that do the same thing. A row for each month/expense item would be ideal. Then you just need to add row for each expense item per month instead of adding more columns. Way easier to maintain in the long run, and way easier to summarize data.
Thanks @jeff_Hager,
I think that is the structure that I have - one record per month per expense type.
I agree that the use of columns in order to get totals by month of overall spend per month as I am doing does not seem to be an optimal approach, hence my question.
I have a very simple database structure - essentially one main transaction table with one row for each expense for each month. And as each item is paid - the transaction status is set from “Not Paid” to “PAid”
Due to the limitation (as I understand it) on rollups only allowing to sum ALL amounts in a column, I can only see setting up separate columns for each month as the only way to get a total spend for each month for all expense types.
Please excuse me if I am missing something.
Ahh, OK. What you need to do is create a query or relation that will match up the month in that table. Then you can apply a Rollup against the query or relation and it will only give you a sum for all the matching items.
2 Likes
Hi @Jeff_Hager - Is there a video that shows how to pass a (user via screen) entered parameter through to a query where that parameter is to be used as the query value?
Specifically, every transaction in this app is coded with one of the following values (eg 202501 …through to 202512) which indicates to which year and month the transaction belongs
If the user can enter a date-code parameter via the screen which can be used as the date-code parameter value, then a query column (utilising the screen entered date code) will be able to provide the functionality that is needed to provide a list of transactions for a specific month and year and the rollup will be able to provide a total for the query elements.
If you are writing the date code to the user profile, then you. should be able to easily access that date code because the user profile is accessible everywhere.
If you are are writing the date code somewhere else, then you can use a single value column to grab that value and populate it in every row in another table.
1 Like
Thank you @Jeff_Hager - just the input I needed - Much appreciated
1 Like