You don't really need separate tables for income and expenditure. You could
have used just one table and either entered debits (expenditure) as a
negative values in the same column as credits (income), or by having separate
credit and debit columns. However, you can still combine the tables in a
UNION query, and base a report on that. Even better you could use the UNION
query to append rows into a new single Transactions table, and then ditch the
original separate tables.
You'd only need to join the tables if individual income items are related to
individual expenditure items on a one-to-one basis, but that's unlikely. I'm
assuming each table has columns (fields) such as TransactionDate, Item,
Amount and, if you are categorizing each transaction Category. If you do
have the latter then you should also have a Categories table with one row per
category and relate the income and expenditure tables to this on the Category
columns, enforcing referential integrity in the relationships so that only
valid categories can be entered in the income and expenditure tables.
Cascade updates should also be enforced in the relationships so that if a
Category is changed in the categories table the change will automatically be
applied to all matching rows in the other two tables. If you do have a
Category column in each of income and expenditure tables, but don't have a
Categories table you can create one and fill it using two append queries, one
based on the income table, the other on the expenditure tables. The Category
column of the categories table (its only column in fact) should be made its
primary key. For each category in the other two tables only one row will be
inserted into the Categories table by virtue of the category column being the
primary key.
With you two existing tables a UNION query, use directly by a report or as
the basis for creating a new single Transactions table would be along the
following lines. You have to do this in SQL view as a UNION query can't be
created in design view so you'd open the query designer; don't add any
tables; switch to SQL view and then enter the SQL:
SELECT YEAR([TransactionDate]) AS [TransactionYear],
MONTH([TransactionDate]) AS [TransactionMonth],
[TransactionDate], [Item], [Amount], [Category]
FROM [Income]
UNION ALL
SELECT YEAR([TransactionDate]) AS [TransactionYear],
MONTH([TransactionDate]) AS [TransactionMonth],
[TransactionDate], [Item], [Amount] * -1, [Category]
FROM [Expenditure];
Multiplying the expenditure amounts by -1 will make them all negative
values. Using the YEAR and MONTH functions to return the year and month of
each transaction will enable you to group the report by month, so when you
create the report based on this query, in report design view you'd group the
report, using the Sorting and Grouping dialogue, first by TransactionYear,
then by TransactionMonth, then by Category and then by TransactionDate. Give
the TransactionMonth group a group header and put a text box in it with a
ControlSource property of:
=Format([TransactionDate], "mmmm yyyy")
This will show the month in the format February 2009.
Give the Category group a group header and put a text box bound to the
Category column in this.
Put controls bound to the TransactionDate, Item and Amount columns in the
detail section. If you set the format property of the Amount control to:
$#,##0.00;($#,##0.00)[Red]
assuming you are using USD that is, the expenditure amounts will show in red
and enclosed in parentheses, the usual convention for negative amounts.
You can include group footers for whatever you want to summarize the values
on, so you could have a Category group footer to get subtotals by category
and a TransactionMonth group footer to get subtotals by month. For grand
totals use the report footer. In each case you'd use text boxes with the
following ControlSource properties:
For an income subtotal:
=Sum(IIf([Amount]>0,[Amount],0))
For an exp[enditure subtotal:
=Sum(IIf([Amount]<0,[Amount],0))
For the balance:
=Sum([Amount])
Finally, if you do decide to create a new single Transactions table, don't
include TransactionYear or TransactionMonth columns in it, just the
TransactionDate. The year and month can be obtained any time in a query with
the Year and Month functions, so the UNION query on which an append query to
fill the Transactions table would be:
SELECT [TransactionDate],[Item], [Amount], [Category]
FROM [Income]
UNION ALL
SELECT [TransactionDate],[Item], [Amount] * -1, [Category]
FROM [Expenditure];
If you decide to create a Transactions table with separate Credit and Debit
columns then the UNION query would be:
SELECT [TransactionDate],[Item], [Amount] AS [Credit],
0 AS [Debit], [Category]
FROM [Income]
UNION ALL
SELECT [TransactionDate],[Item], 0, [Amount], [Category]
FROM [Expenditure];
Note how constant zeros are used in each case to enter this as a value in
the credit or debit column where appropriate. When designing this table
you'd set the Default Value properties of the Credit and Debit columns to
zero and their Required properties to True (Yes) so that they can't be Null
(Nulls cause problems in calculations). In this case the debits, being in a
separate column would be positive amounts, not negative, as with a single
Amount column, so you'd have to subtotal these rather differently to get the
balances in the report:
=Sum([Credit] - [Debit])
Ken Sheridan
Stafford, England
