Access 2007 queries

[Art Vandaley]

Hi,

I have a query something like below:

Year Status
2007 annual leave
2007 annual leave
2007 annual leave
2007 annual leave
2007 annual leave
2007 annual leave
2007 annual leave
2007 annual leave
2007 annual leave
2007 annual leave
2008 annual leave
2008 annual leave
2008 annual leave
2008 annual leave
2008 annual leave

I want to summarize it like below:

Year Status
2007 10 (total amount of "annual leave" in 2007)
2008 5 (total amount of "annual leave" in 2008 until
now)

"count" does not work.

What can I do?

Thanks a lot for any help...

 

[Douglas J. Steele]

What do you mean "'count' does not work"?

Did you create a Totals query? The SQL would be something like:

SELECT [Year], Count([Status])
FROM MyTable
GROUP BY [Year]

You might consider changing the name of your field from Year, by the way.
Year is a reserved word, and reserved words should never be used for your
own purposes. For a comprehensive list of names to avoid, see what Allen
Browne has at http://www.allenbrowne.com/AppIssueBadWord.html

If you cannot (or will not) rename the field, at least put it in square
brackets, as in my example.

 

[KARL DEWEY]

SELECT Year, Count([Status]) AS [Annual leave]
FROM YourTable
GROUP BY Year;

Will get you this --
Year Annual leave
2007 10
2008 5