Using Access to Track House Account

[Altin]

Hi

I have been using an Excel spreadsheet & pivot tables for the past 2 years or so to keep track of our house finances. I did a course recently in Access 2003 & want to use this now to do the same. I am having a bit of trouble currently with summing up the Income & Expense columns & calculating the balance (Income-Expenses). I have a query with the 2 fields & under Totals I have selected "Sum". This works fine, giving me the total for each. When I try to calculate the balance using, "Balance:[Income]-[Expenses]", I get an empty field in my query. How can I fix this?

Also Ive found that this works so long as I just have the 2 fields, Income & Expenses, in the query. If I carry all the fields in, Date, Category, Income & Expenses, then it doesnt work. Can someone explain why this is so please.

 

[BobMcClellan]

Hi
>> using, "Balance:[Income]-[Expenses]", I get an empty field in my query. How can I fix this?
-- Try IIF(IsNull(Income) = true,0,Income) - IIF(IsNull([Expenses]),0,Expenses)
IF one or the other is null, the result will be returned as null unless you handle nulls.

>>Also Ive found that this works so long as I just have the 2 fields, Income & Expenses, in the query. If I carry all the fields in, Date, Category, Income & Expenses, then it doesnt work.
-- There is no reason it should not work because additional columns are added to the query. Is it just a basic SELECT query?
hth,
..bob

 

[Altin]

Hi
>> using, "Balance:[Income]-[Expenses]", I get an empty field in my query. How can I fix this?
-- Try IIF(IsNull(Income) = true,0,Income) - IIF(IsNull([Expenses]),0,Expenses)
IF one or the other is null, the result will be returned as null unless you handle nulls.


When I run this query I still get my 400 or so entries, but they are sorted in order of ascending value, & the Balance column just copies the value for either Income or Expense, & appends a "-" character to the Expense value. What I am looking for when I run it is just 1 row with 3 values, the sum of all Incomes, sum of all Expenses, & a calculated field of Income - Expenses.


>>Also Ive found that this works so long as I just have the 2 fields, Income & Expenses, in the query. If I carry all the fields in, Date, Category, Income & Expenses, then it doesnt work.
-- There is no reason it should not work because additional columns are added to the query. Is it just a basic SELECT query?


Yes its just set to a basic Select query. If I copy all field headers into the query it just results in the whole table being given. If I choose only the Income & Expenses field, & set Total to "Sum", then I get just 2 entries with each column summed up. The I difficulty I am having then is calculating the difference between the 2 fields.

 

[BobMcClellan]

Altin,
if you want to put the table ( or tables ) into a db and attach it, along with an example of your desired result set (posted here), I'd be happy to take a look at it for you.
..bob

 

[Altin]

Bob

Ive attached an example of what I am working on. I have a few records in a table, different deposits & withdrawals over a few days, & a query summing up all the ins & outs & then a field to calculate the balance. If I could get that calculation to work Id be happy.

Cheers

 

[BobMcClellan]

Altin,
After opening the .mdb I can see that you have Categories like Car, Income & Shopping. If you want to aggregate by Category, you can certainly do that easily enough. You can't aggregate by date because the dates in your table are all different.
You would get the same result as a select statement if you group on date and they are all different...
If the objective is simply to calculate the sum of Income - the Sum of Expenses, then this will work for you....

SELECT Sum(tblAccount.Income) AS Inc, Sum(tblAccount.Expense) AS Exp, Sum([Income])-Sum([Expense]) AS Remainder
FROM tblAccount;

( I saved the query with this SQL )
hth,
..bob

 

[Altin]

That works perfectly for what I was trying to get at, which was calculating the balance of the sum of Income - the sum of Expenses. Can I ask please if you wouldnt mind walking me through the formula you used. Im wondering why you chose to rename the fields, like Inc for Income, & then if you could explain what is happening in the formula under the Remainder field. This formula is different too to the formula you have written below, why is this?

Thanks for your time & help

 

[BobMcClellan]

>>Can I ask please if you wouldnt mind walking me through the formula you used.
SELECT Sum(tblAccount.Income) AS Inc, Sum(tblAccount.Expense) AS Exp, Sum([Income])-Sum([Expense]) AS Remainder
FROM tblAccount;

Altin, If you are using the QBE Grid as opposed to the SQL window, the tool bar allows you to select the type of query. IF you select GROUPING query, then just use the SUM function to total the columns you want included in your calculation
The SQL above pretty much shows you exactly what it is doing.

>>Im wondering why you chose to rename the fields, like Inc for Income,
--No reason at all. I just don't like aliasing a column with the actual name of the column.
>>& then if you could explain what is happening in the formula under the Remainder field. This formula is different too to the formula you have written below, why is this?
--It's not different. It's the exact query from the .mdb. I copied and pasted it into the post. As far as the explanation, please see above.
hth,
..bob

 

[Altin]

Thanks for your help Bob