Query in Microsoft Access

Question

I am writing a query in Microsoft Access. I have 3 tables, Income, Expenses, Customers. I want to have the history of the customer, his records on the expenses and his records on income, in one table.

He has two records as income and 1 record as expenses. As a result, I take the two records of income but the one record of expenses is duplicated and is not null as I expected when I run the query.

Can you please help me?

Thank you!

Answer 1

Try UNION query.

SELECT tblCustomer.CUsID, tblCustomer.CusName, tblIncome.Income, null as [Expense]
FROM tblIncome INNER JOIN tblCustomer ON tblIncome.CusID = tblCustomer.CUsID

UNION ALL

SELECT tblCustomer.CUsID, tblCustomer.CusName, Null AS Income, tblExpense.Expense  as [Expense]
FROM tblCustomer LEFT JOIN tblExpense ON tblCustomer.CUsID = tblExpense.CusID