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;
pcalabriaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Doug BishopDatabase 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

Open in new window

Scott PletcherSenior 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
*/
pcalabriaAuthor 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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

pcalabriaAuthor Commented:
@Scott  No Luck.  Syntax error (missing operator) in query expression 'Count(Distinct left(linvoiceNumber,5))"
I don't think it likes distinct. :-(
Scott PletcherSenior 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
PortletPaulEE Topic AdvisorCommented:
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

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

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

Start your 7-day free trial
pcalabriaAuthor 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.