Simple Count Query 1

[MinnKota]

I have a table with the Following format

[Reason],[Date]
NS, 12/05/2003
OA, 12/07/2003
NS, 1/02/2004
AS, 11/05/2003
SS, 12/02/2003
OA, 12/04/2003
NS, 2/05/2004

I am trying to build a count query that returns a total for the reason codes in a certain year (2003). I would like the final product to look like:

[Reason],[Total]
AS, 1
NS, 1
OA, 2
SS, 1

 

[amorous]

Try this:

SELECT REASON, COUNT(*) AS Total FROM yourtable
GROUP BY REASON, [DATE]

-VJ

 

[MinnKota]

Does this return only 2003 reason codes?

 

[flyover789]

try this:

SELECT [Reason], COUNT([Reason])
FROM [yourtbl]
WHERE [Date] >= #01/01/2003# and [Date] <= #12/31/2003
GROUP BY [Reason];

HTH,
fly

Martin Serra Jr.

http://www.mserrasystems.de

 

[flyover789]

oops theres a sharp sign missing, and an alias for the count corrected:

SELECT [Reason], COUNT([Reason]) as CntReasons
FROM [yourtbl]
WHERE [Date] >= #01/01/2003# and [Date] <= #12/31/2003#
GROUP BY [Reason];

HTH,
fly

Martin Serra Jr.

http://www.mserrasystems.de

 

[amorous]

Try this:

SELECT REASON, COUNT(*) AS Total FROM yourtable
WHERE year([DATE])='2003'
GROUP BY REASON

-VJ

 

[MinnKota]

I have it up and running. Thanks.

 

[PHV]

Anyway, avoid to name your fields with reserved words like Date. A lot of headaches in perspective.

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