I need help writing a ms access 2k query
I need a list of customers who have placed 5 or more orders and also spend $1000 or more since January 1, 2018
The problem is that if a customer orders three items at the same time I want to treat it as 1 order
The customer information is straight foward.
Prospects Table has the following fields:
The order information is in a table called InvoiceHistory
It has numeric price and quantity fields, so price*quantity is the amount of the order
InvoiceHistory Table has the following fields:
The tricky part is that the invoicenumber which is a string field uses the first five characters for the invoice number.
Each item is appended to the invoicenumber.
Should only count as one invoice, so left(invoicenumber,5) is acutally the invoice numbers that we want to count to determine the number of orders.
This is as far as I can get. This following query counts all the invoices to calculate the count, so 12345a, b, and c count as three, and it should only count as 1.
Can anyone help???
SELECT Prospects.CustomerNumber, Prospects.CompanyName, Count(InvoiceHistory.InvoiceNumber) AS CountOfInvoiceNumber, Sum([price]*[quantity]) AS Expr1
FROM Prospects INNER JOIN InvoiceHistory ON Prospects.CustomerNumber = InvoiceHistory.CustomerNumber
GROUP BY Prospects.CustomerNumber, Prospects.CompanyName
ORDER BY Sum([price]*[quantity]) DESC;