By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,221 Members | 1,120 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,221 IT Pros & Developers. It's quick & easy.

COUNT Problem

P: 7
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..
Dec 12 '07 #1
Share this Question
Share on Google+
2 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, there.

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

I would go with something like this.
Expand|Select|Wrap|Line Numbers
  1. SELECT Year([dteDate]) AS intYear, Month([dteDate])  AS intMonth, Count(tblDates.dteDate) AS CountOfdteDate
  2. FROM tblDates
  3. GROUP BY Year([dteDate]), Month([dteDate]);
  4.  
Regards,
Fish

P.S. Or even the following to see the names of months.
Expand|Select|Wrap|Line Numbers
  1. SELECT Year([dteDate]) AS intYear, Format(DateAdd("m",Month([dteDate])-1,#1/1/1#),"mmmm") AS txtMonth, Count(tblDates.dteDate) AS CountOfdteDate
  2. FROM tblDates
  3. GROUP BY Year([dteDate]), Month([dteDate]);
  4.  
Dec 12 '07 #2

NeoPa
Expert Mod 15k+
P: 31,660
...
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??
Dec 13 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.