Combine two tables into one recordset? 1

[BiggerBrother]

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

income:
date transaction amount

expenses:
date expense amount

I need to be able to return a recordset that has the following:

date transaction/expense expense.amount income.amount

as found on any standard bank statment type report.
I need to be able to order and compute the balance by date. I know the coding for the ordering and compute function, but I'm not sure how to return the data from the two tables.

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

Thanks again

BB

 

[SonOfEmidec1100]

Really need more info to give a specific answer, however this example assumes the date the only link and returns 1 summary row for each date.

SELECT i.date, sum(isnull(e.amount,0)) as expense, sum(isnull(i.amount,0)) as income
FROM income i FULL OUTER JOIN expense e ON i.date = e.date
GROUP by i.date
ORDER BY i.date

May be this helps.

 

[BiggerBrother]

tried this code:
"SELECT d.RecordDate as RowDate, " _
& "sum(isnull(e.ExpAmount,0)) as expenses, sum(isnull(d.DonAmount,0)) as income " _
& "FROM Donation d FULL OUTER JOIN Expense e ON d.RecordDate = e.ExpDate " _
& "GROUP by d.RecordDate " _
& "ORDER by d.RecordDate "
and it returned an error failed to open database.

I need to return all of the lines of both databases, combined into one table.

date transaction out in total?
22/10/03 paid gran 10.00 -10.00
23/10/03 got paid 90.00 80.00

Not quite sure where i'm going wrong. There must be an easy way, but I can't see it for looking.

Thanks for your help.

BB

 

[BlackKnight]

Hi,

You can use an UNION query like:

SELECT * FROM Income UNION SELECT * FROM Expense

Have a good one!
BK

 

[BiggerBrother]

Thanks BK.