drinian wrote:
> 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.
>

looks like you're using a fourth table called invoiceitem. would you be
able to post the output of these commands:
desc Invoices;
desc InvoiceStatus;
desc SaleItem;
desc InvoiceItem;

--
lark -- hamzee@sbcdeglobalspam.net
To reply to me directly, delete "despam".