Hi I have a query is used for the control source of a report, Monthly
Statements

The query was running fine when it was simply showing all debits, now Ive
tried to adapt it by joining another table, transaction which stores details
of all customer payments, as well as supplier payments. Here is the original
query:

SELECT DISTINCT tblAccount.AccountIndex, tblSalesOrder.CustomerOrderNumber,
tblSalesInvoice.InvoiceDate, tblSalesInvoice.SalesInvoiceNumber,
tblSalesInvoice.TotalNett, tblVAT.VATValue, [TotalNett]*(([VatValue]/100)+1)
AS Debit
FROM tblVAT INNER JOIN ((tblAccount INNER JOIN tblSalesOrder ON tblAccount.
AccountIndex=tblSalesOrder.AccountIndex) INNER JOIN ((tblDespatch INNER JOIN
tblSalesOrderLine ON tblDespatch.SalesOrderNumber=tblSalesOrderLine.
SalesOrderNumber) INNER JOIN tblSalesInvoice ON tblDespatch.
DespatchNumber=tblSalesInvoice.[Despatch Number]) ON (tblSalesOrder.
SalesOrderNumber=tblDespatch.SalesOrderNumber) AND (tblSalesOrder.
SalesOrderNumber=tblSalesOrderLine.SalesOrderNumber)) ON tblVAT.
VATRate=tblSalesOrderLine.VATRate
ORDER BY tblAccount.AccountIndex, tblSalesInvoice.InvoiceDate;

After adapting it to 'supposedly' show the details of any payments
it now looks like :

SELECT DISTINCT tblAccount.AccountIndex, tblSalesOrder.CustomerOrderNumber,
tblSalesInvoice.InvoiceDate, tblSalesInvoice.SalesInvoiceNumber,
tblSalesInvoice.TotalNett, tblVAT.VATValue, [TotalNett]*(([VatValue]/100)+1)
AS Debit, tblTransaction.TypeOfPosting, IIf([TypeOfPosting]="Customer
Payment",[Amount],0) AS Credit
FROM tblTransaction INNER JOIN (tblVAT INNER JOIN ((tblAccount INNER JOIN
tblSalesOrder ON tblAccount.AccountIndex = tblSalesOrder.AccountIndex) INNER
JOIN ((tblDespatch INNER JOIN tblSalesOrderLine ON tblDespatch.
SalesOrderNumber = tblSalesOrderLine.SalesOrderNumber) INNER JOIN
tblSalesInvoice ON tblDespatch.DespatchNumber = tblSalesInvoice.[Despatch
Number]) ON (tblSalesOrder.SalesOrderNumber = tblSalesOrderLine.
SalesOrderNumber) AND (tblSalesOrder.SalesOrderNumber = tblDespatch.
SalesOrderNumber)) ON tblVAT.VATRate = tblSalesOrderLine.VATRate) ON
tblTransaction.TransactionNumber = tblSalesInvoice.TransactionNumber
ORDER BY tblAccount.AccountIndex, tblSalesInvoice.InvoiceDate;

The query works but only showing the records that have had a payment. I know
this is because of the INNER JOIN, but could anybody tell me is there a way
to display the result of the first query as well as being able to display any
payment details made from the transaction table??

Thank you