Count month year in column

[UBNSLICK]

I need number of entries by each month per year.

I want the following to show in a cell as:

4
1
3

7/16/07
7/16/07
7/17/07
7/21/07
9/10/07
1/10/08
1/15/08
1/26/08

Thanks DC

 

[raskew]

Hi -

A simple totals query will get it.
Copy/paste this SQL to a new query,
then correct table/field names.

SELECT Month([[COLOR="red"]ExpDte[/COLOR]]) AS Expr1, Count(Month([[COLOR="red"]ExpDte[/COLOR]])) AS Expr2
FROM [COLOR="red"]tblTransfer[/COLOR]
GROUP BY Month([[COLOR="red"]ExpDte[/COLOR]]);

HTH - Bob

 

[UBNSLICK]

Sorry I need to give you more info I'm pulling info out access then going back to Excel to find how many for each month per year. Is this possible, I have tried count, countif, dcount etc. and keep getting Jan-00 or 1/1/00 depending on how I have the cell formatted.

 

[raskew]

The code provided, which assumed an Access table with
the date field in date/time data format, returns this:

Expr1	Expr2
1	112
2	82
3	50
4	70
5	59
6	59
7	62
8	53
9	84
10	84
11	59
12	89

Now I'm confused:
1) Are you doing this in Access or Excel?
2) If in Access, is your date field in date/time data format or is it a text field?

Bob

 

[UBNSLICK]

Sorry to confuse, this is what I am after.



Drawing Release By Month/Year


Jan-07 ___ 3
Feb-07 ___ 3
Mar-07 ___ 1
Apr-07 ___ 4
Feb-08 ___ 1
Mar-08 ___ 3
Apr-08 ___ 1
May-08 ___ 2


DRAWING TITLE ITEM TYPE RELEASED
3453465 LAYOUT NC REV 1/23/07
6456457 LAYOUT NC REV 1/23/07
M6565-3 GENERAL ARR NC REV 1/31/07
68678675-1 MAX D NC REV 2/1/07
56745734 LAYOUT NC REV 2/1/07
M65656-2 GENERAL ARR NC REV 2/23/07
7567856 ACCESS PANELS NC REV 5/29/07
457457 LAYOUT NC REV 2/1/08
M545-2 GENERAL ARR A REV 3/25/08
4567457 LAYOUT NC REV 3/1/08
M545-3 GENERAL ARR A REV 3/25/08
787867 ELEVATOR CTRL NC REV 4/15/08
488899 LAYOUT NC REV 5/19/08
678678 CTRL SYS NC REV 5/19/08

 

[raskew]

OK.

This query:

SELECT
    Format([ExpDte],"mmm-yy") AS MoYr
  , Count(tblTransfer.ExpDte) AS countofID
FROM
   tblTransfer
GROUP BY
   Format([ExpDte],"mmm-yy")
  , Year([ExpDte])
  , Month([ExpDte])
HAVING
   ((Not (Format([ExpDte],"mmm-yy")) Is Null))
ORDER BY
   Year([ExpDte])
  , Month([ExpDte]);

...returns this:

MoYr    countofID
Jan-08  44
Feb-08  16
Mar-08  46
Apr-08  70
May-08  59
Jun-08  59
Jul-08  62
Aug-08  53
Sep-08  84
Oct-08  84
Nov-08  59
Dec-08  89
Jan-09  68
Feb-09  66
Mar-09  4

HTH - Bob

 

[UBNSLICK]

I forgot te tell you I was in EXCEL.

So countif is out of the question?

does all theis go on one line? I am a newbie and need a little more put here etc if pos.

Thanks

 

[Brianwarnock]

In Excel copy the date to the left most column you, may need to first insert a col, make a custom format for the cells mmm-yy and then use subtotalling found under Data from the menu bar.

Brianr

 

[UBNSLICK]

Thats It!

Thanks

Boss liked all is good!!!

 

[Brianwarnock]

It good to make the boss happy.

Brian