# Need help writing a query to total sale and number of orders

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:

CustomerNumber
CustomerName

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:
Price
Quantity
InvoiceNumber

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.

For example:

12345a
12345b
12345c

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
WHERE (((InvoiceHistory.ShippedDate)>#1/1/2018#))
GROUP BY Prospects.CustomerNumber, Prospects.CompanyName
ORDER BY Sum([price]*[quantity]) DESC;
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Database DeveloperCommented:
Try this. I don't have access or your data so can't really test but syntactically it is correct.
``````SELECT Prospects.CustomerNumber, Prospects.CompanyName, left(InvoiceHistory.InvoiceNumber, 5) AS InvoiceNumber, Sum([price]*[quantity]) AS Expr1, count(*) as InvoiceCount
FROM Prospects INNER JOIN InvoiceHistory ON Prospects.CustomerNumber = InvoiceHistory.CustomerNumber
WHERE (((InvoiceHistory.ShippedDate)>#1/1/2018#))
GROUP BY Prospects.CustomerNumber, Prospects.CompanyName, left(InvoiceHistory.InvoiceNumber, 5)
HAVING count(*) >= 5 and Sum([price]*[quantity]) >= 1000.00
``````
Senior DBACommented:
Hmm, Access apparently doesn't support DISTINCT in COUNT.  That's a pain.  Will have to use a subquery then.

SELECT IH.CustomerNumber,
SUM(IH.price*IH.quantity) AS SpendTotal,
COUNT(DISTINCT LEFT(InvoiceNumber, 5)) AS OrderCount
FROM (
SELECT DISTINCT CustomerNumber, LEFT(InvoiceNumber, 5) AS InvoiceNumberOnly
FROM InvoiceHistory
WHERE IH.ShippedDate>=#01/01/2018#
) AS IH_Inv_Count
INNER JOIN InvoiceHistory IH ON IH.CustomerNumber = IH_Inv_Count.CustomerNumber AND
IH.InvoiceNumber LIKE IH_Inv_Count.InvoiceNumberOnly + '%'
WHERE IH.ShippedDate>=#01/01/2018#
GROUP BY IH.CustomerNumber
HAVING COUNT(*) >= 5 AND
SUM(IH.price*IH.quantity) >= 1000.00

/* old version, not valid in Access
SELECT IH.CustomerNumber,
SUM(IH.price*IH.quantity) AS SpendTotal,
COUNT(DISTINCT LEFT(InvoiceNumber, 5)) AS OrderCount
FROM InvoiceHistory IH
WHERE
IH.ShippedDate>=#01/01/2018#
GROUP BY IH.CustomerNumber
HAVING COUNT(*) >= 5 AND
SUM(IH.price*IH.quantity) >= 1000.00 AND
COUNT(DISTINCT LEFT(InvoiceNumber, 5)) >=5
*/
Author Commented:
@Doug  No syntax errors but the code did not perform as need.. Numerous missing buyers and many buyers showing up in multiple rows... which is wrong.

Still stuck
Author Commented:
@Scott  No Luck.  Syntax error (missing operator) in query expression 'Count(Distinct left(linvoiceNumber,5))"
I don't think it likes distinct. :-(
Senior DBACommented:
Very true :-(.  But I did change it to try to remove the DISTINCT.

I hope this works because I'm running out of ways to write Access SQL.

SELECT IH.CustomerNumber,
SUM(IH.price*IH.quantity) AS SpendTotal,
COUNT(DISTINCT LEFT(InvoiceNumber, 5)) AS OrderCount
FROM (
SELECT DISTINCT CustomerNumber, LEFT(InvoiceNumber, 5) AS InvoiceNumberOnly
FROM InvoiceHistory
WHERE IH.ShippedDate>=#01/01/2018#
) AS IH_Inv_Count
INNER JOIN InvoiceHistory IH ON IH.CustomerNumber = IH_Inv_Count.CustomerNumber AND
IH.InvoiceNumber LIKE IH_Inv_Count.InvoiceNumberOnly + '%'
WHERE IH.ShippedDate>=#01/01/2018#
GROUP BY IH.CustomerNumber
HAVING COUNT(*) >= 5 AND
SUM(IH.price*IH.quantity) >= 1000.00
I suspect you need 2 group by's:
``````SELECT
IH.CustomerNumber
, SUM( IH.InvoiceAmount )       AS SpendTotal
, COUNT( IH.InvoiceNumberOnly ) AS OrderCount
FROM (
SELECT
LEFT( InvoiceNumber, 5 ) AS InvoiceNumberOnly
, CustomerNumber
, SUM( price * quantity )  AS InvoiceAmount
FROM InvoiceHistory
WHERE ShippedDate >= #01/01/2018#
GROUP BY
LEFT( InvoiceNumber, 5 )
, CustomerNumber
) AS IH
GROUP BY
IH.CustomerNumber
HAVING COUNT( IH.InvoiceNumberOnly ) >= 5
AND SUM( IH.InvoiceAmount ) >= 1000.00
``````

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Thanks everyone for your help.  I tried all of the queries.
PortletPauls code worked exactly as desired.

As for the rest of you, I definitely appreciate your contributions.  I was beginning to wonder what was wrong with me, and why I was working on this so long.  Obviously it was very my head!

Thanks again Paul@
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.