print cancelled invoices

[mseo]

hi,
I have a report for printing sales journal and there are many invoices which cancelled or have no data and when I need to print a report all those invoices don't appear within the report i know that is because the invoice detail has no data so I used the nz function within the query but no thing has been changed
I need any help with this problem
thanks for any help you may provide me

[ChipR]

Assuming your report is sourced directly on the query, what's the SQL for your query? Please use the [ code ] [ /code ] tags to make it readable.

[nico5038]

You should use a so-called "outer join" when linking to the details table.
Just double click (in the design mode of the query) the connection line between the tables (I assume the InvoiceID is the key/foreign key) and select the second or third option, making the Invoice the master table and the details the "slave".

Nic;o)

[mseo]

the code I have used is

SELECT tbl_SalesInvoices.InvoiceNo, tbl_SalesInvoices.Customer_ID, tbl_SalesInvoices.Date, CCur(Sum(nz([UnitPrice],0)*(nz([Quantity],0)))) AS Extended, CCur(Sum((nz([UnitPrice],0)*nz([Quantity],0))*10/100)) AS [Tax 10%], CCur(Sum((((nz([UnitPrice],0)*nz([Quantity],0))+(nz([UnitPrice],0)*nz([Quantity],0))*10/100)))) AS [Grand Total], tbl_Customer.CompanyName, tbl_SalesInvoices.Cancelled
FROM (tbl_Customer INNER JOIN tbl_SalesInvoices ON tbl_Customer.CustomerID = tbl_SalesInvoices.Customer_ID) INNER JOIN tbl_SalesInvoicesDetails ON tbl_SalesInvoices.InvoiceNo = tbl_SalesInvoicesDetails.InvoiceNo
GROUP BY tbl_SalesInvoices.InvoiceNo, tbl_SalesInvoices.Customer_ID, tbl_SalesInvoices.Date, tbl_Customer.CompanyName, tbl_SalesInvoices.Cancelled, tbl_Customer.CustomerID
ORDER BY tbl_SalesInvoices.InvoiceNo;

I just want print all the invoices even the invoice detail is null
thanks

[nico5038]

Try:

SELECT tbl_SalesInvoices.InvoiceNo, tbl_SalesInvoices.Customer_ID, tbl_SalesInvoices.Date, CCur(Sum(nz([UnitPrice],0)*(nz([Quantity],0)))) AS Extended, CCur(Sum((nz([UnitPrice],0)*nz([Quantity],0))*10/100)) AS [Tax 10%], CCur(Sum((((nz([UnitPrice],0)*nz([Quantity],0))+(nz([UnitPrice],0)*nz([Quantity],0))*10/100)))) AS [Grand Total], tbl_Customer.CompanyName, tbl_SalesInvoices.Cancelled
FROM (tbl_Customer INNER JOIN tbl_SalesInvoices ON tbl_Customer.CustomerID = tbl_SalesInvoices.Customer_ID) RIGHT JOIN tbl_SalesInvoicesDetails ON tbl_SalesInvoices.InvoiceNo = tbl_SalesInvoicesDetails.InvoiceNo
GROUP BY tbl_SalesInvoices.InvoiceNo, tbl_SalesInvoices.Customer_ID, tbl_SalesInvoices.Date, tbl_Customer.CompanyName, tbl_SalesInvoices.Cancelled, tbl_Customer.CustomerID
ORDER BY tbl_SalesInvoices.InvoiceNo;
 

I've changed an INNER JOIN into a RIGHT JOIN. I'm however never 100% sure or it needs to be a LEFT or RIGHT join, so you must check first in the query editor that an arrow is pointing from the tbl_SalesInvoices to tbl_SalesInvoicesDetails and not the other way around...

Nic;o)