summarising financial data (a97sr2)

Topic

New Reply

The application is my do-it-yourself finances, where I track what on earth I actually do with my salary. My previous experience with databases has been data generation, and now I’m coming to grips with condensing existing data into reports. Please understand I have no financial training, so the words I use may sound funny to those who know what they’re talking about.

I have a table tblJournal with fields including Date, From, To, Amount (and some other fields as well, best described as Why). From and To contain account names to represent my savings account, credit card, investment accounts etc.

I have already created a report which summarises each month’s income and expenses in terms of “why”. The date formatting in the query was borrowed from a query created by a wizard, and I can’t say I understand the expression, which is something like
GROUP BY Year(tblJournal.Date) * 12 + Datepart(“m”, tblJournal.Date) – 1 … and then some date formatting

1. I want to create a query which will tell me cashflow into and out of each account, on a monthly basis. I will use this for both a summary and also to retrieve just a single months data.

2. I then want to make that query flexible (monthly, yearly including calendar, financial and specified years).

3. Once I understand the above strange date expression, I want to do the same to my existing expense report.

As yet there is not a table of account names, but this will be trivial to create. From the table structure above, you can see that cash flow out is when the account is listed in the From field, and cash flow into an account when it is listed in the To field. I have queries that will give me either cash flow in or cashflow out, but haven’t worked out how to combine the two.

I guess that a crosstab is looking good here perhaps pivoting on account names, but I’m in unfamiliar territory, and suggestions on how to proceed are most welcome.

Thanks

Reply | Quote

Replies

The expression Year(tblJournal.Date) * 12 + Datepart(“m”, tblJournal.Date) – 1 calculates a sequential month number. The year is multiplied by 12, the number of months in a year, then the month number minus one is added.
Examples:
If tblJournal.Date is in July, 2004, the expression evaluates to 2004*12 + 7 – 1 = 24054
If tblJournal.Date is in November, 2004, the expression evaluates to 2004*12 + 11 – 1 = 24058
Grouping on this expression effectively means grouping by calendar month.

To get the cash flows combined into one query, you will need to create several queries:
– A totals query that groups by the From field and sums Amount, within the desired time period. Label the sum Outflow or something like that.
– A totals query that groups by the To field and sums Amount, within the desired time period. Label the sum Inflow or something like that.
– A query based on the Accounts table (you do need one) and the two above queries, with a left join from the Accounts table to each of the queries; add Account from the accounts table, and the Outflow and Inflow fields from the queries.

Reply | Quote

Thanks Hans.

Thanks for your clear explanation, and I am away. My main mistake was not to eliminate other months’ sums in the first two queries, I was trying to do that in the final query. Because I ended up with an awful mess, I started thinking I needed a crosstab. The other reason for the crosstab was to see each months’ data in the same query – still thinking about that. I had also tried to do a right join and left join with tblAccounts in the middle. I think that might have been OK if a little ugly.

Marty

ps. The cashflow report I had in mind turns out to be practically useless! Having realised this, I found a way to reorganise the report to give something more meaningful. Make more mistakes, and learn more!

Reply | Quote

Thanks Hans.

Thanks for your clear explanation, and I am away. My main mistake was not to eliminate other months’ sums in the first two queries, I was trying to do that in the final query. Because I ended up with an awful mess, I started thinking I needed a crosstab. The other reason for the crosstab was to see each months’ data in the same query – still thinking about that. I had also tried to do a right join and left join with tblAccounts in the middle. I think that might have been OK if a little ugly.

Marty

ps. The cashflow report I had in mind turns out to be practically useless! Having realised this, I found a way to reorganise the report to give something more meaningful. Make more mistakes, and learn more!

Reply | Quote

The expression Year(tblJournal.Date) * 12 + Datepart(“m”, tblJournal.Date) – 1 calculates a sequential month number. The year is multiplied by 12, the number of months in a year, then the month number minus one is added.
Examples:
If tblJournal.Date is in July, 2004, the expression evaluates to 2004*12 + 7 – 1 = 24054
If tblJournal.Date is in November, 2004, the expression evaluates to 2004*12 + 11 – 1 = 24058
Grouping on this expression effectively means grouping by calendar month.

To get the cash flows combined into one query, you will need to create several queries:
– A totals query that groups by the From field and sums Amount, within the desired time period. Label the sum Outflow or something like that.
– A totals query that groups by the To field and sums Amount, within the desired time period. Label the sum Inflow or something like that.
– A query based on the Accounts table (you do need one) and the two above queries, with a left join from the Accounts table to each of the queries; add Account from the accounts table, and the Outflow and Inflow fields from the queries.

Reply | Quote