Help with multiple queries (1 Viewer)

[DenisCooper]

Hi,

I have two tables, one showing expenses and one showing payments received. They are then linked to another table with all property details.

I am try to write a query which shows the payments received and expenses paid on each property. I can do this as seperate queries, but then when i create the third query to show everything together it generates around 15000 records.

my queries are shown below

Query to show payments received by tenant per property.

SELECT tblProperty.DisplayName, tblTenant.DisplayName, tblPayments.DateReceived, tblPayments.AmountReceived
FROM (tblTenant INNER JOIN (tblProperty INNER JOIN tblLease ON tblProperty.PropertyID = tblLease.cboProperty) ON tblTenant.TenantID = tblLease.cboTenant) INNER JOIN tblPayments ON (tblTenant.TenantID = tblPayments.cboTenant) AND (tblTenant.TenantID = tblPayments.cboTenant);

Query to show expenses made per property.

SELECT tblProperty.DisplayName, tblExpenses.DatePaid, tblExpenses.AmountPaid, tblExpenses.ExpenseType
FROM tblProperty INNER JOIN tblExpenses ON tblProperty.PropertyID = tblExpenses.ExpenseLinkedToProperty;

End goal - to create a report with details from both to show all expenses and all payments received by property.

Any help greatly appreciated.

Thanks
Denis

 

[CJ_London]

Do you mean your report has two areas - one show receipts and one showing expenses or do you mean you want one list of both receipts and expenses - i.e. like a bank statement?

If the former, I would have two subreports on your main report, if the latter you can use a union query, but before you do you need to ensure both queries have the same number of columns and column types.

 

[DenisCooper]

great - thanks for your help