Show zero totals - do I need a crosstab query? I have three tables: Invoices, InvoiceStatus, SaleItem
I want to run a query that totals the invoices by the sale item.
Here's my query:
SELECT Sum(Invoices.TotalCharge) AS Amount, SaleItem.SaleItem,
SaleItem.SaleItemID FROM SaleItem RIGHT JOIN ((Invoices LEFT JOIN
InvoiceStatus ON Invoices.InvoiceStatusID =
InvoiceStatus.InvoiceStatusID) LEFT JOIN InvoiceItem ON
Invoices.InvoiceNumber = InvoiceItem.InvoiceNumber) ON
SaleItem.SaleItemID = InvoiceItem.SaleItemID WHERE
InvoiceStatus.InvoiceStatus != 'Void' AND MONTH(ServiceDate) = '02'
AND YEAR(ServiceDate) = '2007' GROUP BY SaleItem.SaleItem ORDER BY
SaleItem.SaleItem
This works fine, but I would like the query to show a zero value for
the sale items not purchased. Currently this query will only show the
totals of actual sale items that were sold. Is there a way to force
the query to always show all sales items, even the ones that total to
zero (no sale item sold that month)?
Also, is this what a crosstab query is?
I appreciate any advice or input. |