Count the number of X in a total query?

[EdAROC]

I can't believe I couldn't find this question somewhere...

Question to me: How many quotes did we generat per customer last year?

Two Tables: Quotes and Customer

I started a Total Query
CustomerID - Grouped
CSNAME - Last (to capture something, in case name changed)
? How do I get a count? (see below for desired output)

Filter Quotes Where QuoteDate "Between [Begin Date] And [End Date]"

This is all we need:
CustomerID CSNAME ?
123 ABC 17
456 FGH 202
789 XYZ 13

 

[PHV]

what are the relationships between Quotes and Customer ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[EdAROC]

They are linked on CustomerID.
CSNAME is in the Customers table.

I know that all I need in this query is:
Field (Table)
CustomerID (Quotes)- group
CSNAME (Customer) - last
QuoteDate (Quotes) - where 'Between [Begin Date] AND [End Date]
? <-- something, probably in (Quotes), so that I can count the number of quotes.

 

[PHV]

SELECT Q.CustomerID, C.CSNAME, Count(*) AS NumberOfQuotes
FROM Quotes AS Q INNER JOIN Customer AS C ON Q.CustomerID = C.CustomerID
WHERE Q.QuoteDate Between [Begin Date] And [End Date]
GROUP BY Q.CustomerID, C.CSNAME

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886