How can you combine two tables? 1

[BiggerBrother]

I have two tables. One is for expenses and the other for income.

income:
date transaction amount
23/11/03 Got Paid 100.00

expenses:
date expense amount
24/11/03 Paid rent 80.00
I need to be able to return a recordset that has the following:

date transaction/expense expense.amount income.amount
23/11/03 Got paid 100.00
24/11/03 Paid rent 80.00

as found on any standard bank statment type report.
I need to be able to order and compute the balance by date, and I'm not sure where to begin.

Probably easy, but I'm fairly new to this.

Thanks again

BB

 

[DanChard]

Try this:

SELECT date, transaction as [transaction/expense], format(null,'Currency') as [expense_amount], format(amount,'Currency') as [income_amount]
from income
union
select date, expense, format(amount,'Currency'), format(null,'Currency')
from expenses
order by date;

 

[BiggerBrother]

Thank you - brilliant - two days and finally I have some code that works. I would have never guessed at that. However, I'm using access function that adds each column. As the columns have null values in it returns a function error. If I replace the null with 0, it works perfectly. How can have a null value, and still have the function working, so that if the value is 0, I get " " instead of £0.00.

Thank you again

BB

 

[DanChard]

You can use IIF to control what's displayed in place of particular values. In this case, the string " " (a space) is displayed in place of the value 0:

SELECT date, transaction as [transaction/expense], format(null,'Currency') as [expense_amount], iif(format(amount,'Currency')=0,' ',format(amount,'Currency')) as [income_amount]
from income
union
select date, expense, iif(format(amount,'Currency')=0,' ',format(amount,'Currency')), format(null,'Currency')
from expenses
order by date;

Hope this works OK for you.

 

[p27br]

I.M.O. , you should have designed your DB so that you have only one table with all transactions. You should then add a field in which you enter either 'expense' or 'income'.

 

[DanChard]

I have something similar to this which I use for keeping track of money:

payments
========
payment_id (AutoNum - PK)
payment_type (Text[3] - 'IN' or 'OUT')
payment_amount (Currency)
payment_detail (Text[50])
date_of_payment (Date)
payment_confirmed (Yes/No)
payment_entered_on (Date)

I still have to use similar SQL to that posted here (in my first post) to get something out of it that resembles a statement.

 

[BiggerBrother]

Thanks DanChard. You have been a great help. P27BR, the db has about 20 tables within, and the expenses and income tables have rough 18 different columns in each. If I combine the tables, I would then have a table of rough 30 columns where 12 contain nulls in each row. This style of table would then consume more disk space for what is already a very large file.

Thanks again

BB

 

[p27br]

I see what you mean, but disk space isn't really that expensive nowadays, is it.
Note that the union query matches columns from each select statement by their position in the select statement and not by their names.
you could use a one-to-one relationship :

tblTransaction 1-----------1 tblExpenseDetails
1-----------1 tblIncomeDetails