COUNT Problem

[tak786]

hello..

i have created query which has 1 "date field" which I need to count the instances of each month.

i.e

field 1: date field = count of "*01/2007"
field 2: date field = count of "*02/2007"
field 3: date field = count of "*03/2007"
etc....

The main problem being that I am creating extra fields from the one date field and this will not count in one query do you have any suggestions..

Any help will be much appreciated

Thanks..

[FishVal]

Hi, there.

[tblDates] - name of the table
[dteDate] - table field containing date

I would go with something like this.
[code=sql]
SELECT Year([dteDate]) AS intYear, Month([dteDate]) AS intMonth, Count(tblDates. dteDate) AS CountOfdteDate
FROM tblDates
GROUP BY Year([dteDate]), Month([dteDate]);
[/code]

Regards,
Fish

P.S. Or even the following to see the names of months.
[code=sql]
SELECT Year([dteDate]) AS intYear, Format(DateAdd( "m",Month([dteDate])-1,#1/1/1#),"mmmm") AS txtMonth, Count(tblDates. dteDate) AS CountOfdteDate
FROM tblDates
GROUP BY Year([dteDate]), Month([dteDate]);
[/code]

[NeoPa]

...
The main problem being that I am creating extra fields from the one date field and this will not count in one query do you have any suggestions..
...

Up to this point everything makes perfect sense.
At this point it breaks down into making no sense at all. What do you mean??