Date Query (1 Viewer)

[Mickster]

Hi there,

I have a date field in my table with the following format mm/dd/yyyy. The dates in my table vary from all the way back from 1994 to today. What I am trying to do is count the number of records in each year. So have it output something like this


Year Number of Records
1994 5
1995 6
1996 12
1997 13
..
...
....
so on.....

Any suggestions?

Thanks for help

 

[KenHigg]

Do a totals query with a group and a count column...

 

[Matt Greatorex]

Does this work:

select
count(Format([date],"YYYY")) as DateCount,
format([date],"YYYY") as ActualYear
from tablename
group by [date]
order by [date];

incidentally, the fact that the field is called date (a reserved word) may cause you some problems later on.