I was able to create a report using a customer set with customer as the parent and invoiceline as the child giving me items ordered in the past.

My problem is I only want the last time an item was ordered and want to eleminate some items completly such as shipping.

When I try to filter out items like shipping it takes out any invoice line item as well.

When it comes to getting only the last time an item was ordered I'm at a complete loss.

Any help would be greatly appreciated....Thanks

For items ordered by a customer, in part name order with last date ordered first:

SELECT TxnDate, InvoiceLineItemRefFullName, sum(InvoiceLineQuantity) as Qty
FROM InvoiceLine
WHERE CustomerRefFullName='Cook, Brian:Kitchen' and InvoiceLineItemRefFullName is not null
Group by InvoiceLineItemRefFullName, TxnDate
Order by InvoiceLineItemRefFullName, TxnDate desc

Or for the last time a item was ordered by a customer:

SELECT Top 1 TxnDate, InvoiceLineItemRefFullName, sum(InvoiceLineQuantity) as Qty
FROM InvoiceLine
WHERE CustomerRefFullName='Cook, Brian:Kitchen' and InvoiceLineItemRefFullName = 'Appliance'
Group by InvoiceLineItemRefFullName, TxnDate
Order by InvoiceLineItemRefFullName, TxnDate desc

I'm not to sure what the question is?, but QODBC does support "BETWEEN", for example:-

SELECT TxnDate, InvoiceLineItemRefFullName, sum(InvoiceLineQuantity) as Qty
FROM InvoiceLine
WHERE CustomerRefFullName='Cook, Brian:Kitchen'
and InvoiceLineItemRefFullName between 'Appliance' and 'Framing'
Group by InvoiceLineItemRefFullName, TxnDate
Order by InvoiceLineItemRefFullName, TxnDate desc

SELECT TxnDate, InvoiceLineItemRefFullName, sum(InvoiceLineQuantity) as Qty
FROM InvoiceLine
WHERE CustomerRefFullName='Cook, Brian:Kitchen'
and InvoiceLineItemRefFullName between TxnDate='#Date_Start#'  and TxnDate='#Date_End#'
Group by InvoiceLineItemRefFullName, TxnDate
Order by InvoiceLineItemRefFullName, TxnDate desc

Its is possible?

Yes, but you need to get the syntax correct:

SELECT TxnDate, InvoiceLineItemRefFullName, sum(InvoiceLineQuantity) as Qty
FROM InvoiceLine
WHERE CustomerRefFullName='Cook, Brian:Kitchen'
and TxnDate between {d'2007-11-01'} and {d'2007-12-01'}
Group by InvoiceLineItemRefFullName, TxnDate
Order by InvoiceLineItemRefFullName, TxnDate desc

Or in your case:

SELECT TxnDate, InvoiceLineItemRefFullName, sum(InvoiceLineQuantity) as Qty
FROM InvoiceLine
WHERE CustomerRefFullName='Cook, Brian:Kitchen'
and TxnDate between '#Date_Start#' and '#Date_End#'
Group by InvoiceLineItemRefFullName, TxnDate
Order by InvoiceLineItemRefFullName, TxnDate desc

cool.

I execute this query:

Receive:

Can Liners:40 x 48:62000 Trash Bag 2.00000
Can Liners:40 x 48:62000 Trash Bag 2.00000
Can Liners:40 x 48:62000 Trash Bag 2.00000
Can Liners:40 x 48:62000 Trash Bag 2.00000
Can Liners:40 x 48:62000 Trash Bag 1.00000
Can Liners:40 x 48:62000 Trash Bag 3.00000
Can Liners:40 x 48:62000 Trash Bag 2.00000
Can Liners:40 x 48:62000 Trash Bag 3.00000
Can Liners:40 x 48:62000 Trash Bag 1.00000
Can Liners:40 x 48:62000 Trash Bag 2.00000
Can Liners:40 x 48:62000 Trash Bag 2.00000
Can Liners:40 x 48:62000 Trash Bag 1.00000
Can Liners:40 x 48:62000 Trash Bag 5.00000

Possible receive not duplicates items or execute statement sum(InvoiceLineQuantity)  ?

Thank you for attention.

Regards,

MacTorvald

Just remove the TxnDate from the group by and order by conditions like this:

SELECT * FROM OPENQUERY(QODBC, 'SELECT InvoiceLineItemRefFullName,sum(InvoiceLineQuantity) as Qty FROM InvoiceLine WHERE CustomerRefFullName=''Super 8 Motel -Orlando'' and TxnDate between {d''2007-01-04''} and {d''2007-09-04''} and InvoiceLineItemRefFullName is not null Group by InvoiceLineItemRefFullName Order by InvoiceLineItemRefFullName')

Ok.. cool! Perfect!

Thank you very much

Regards

MacTorvald