count off

[JRS]

I am tryimng to run tjhis query and it is not doing what I want it to do. I
have types and I want a count by type for the month of jan, feb, mar, etc.
so I would expect to see jan with 5 types amd a count of each. I hope that
makes sense. Can you tell what is the matter? thanks

also the date came over in the import as 2/3/2008 5:11:28 PM so in design
view on the format line I did a mmm dd to get Mar 08.


SELECT Count(KANSAS.[+Log Date]) AS [CountOf+Log Date], KANSAS.[+Log Date],
KANSAS.Type
FROM KANSAS
GROUP BY KANSAS.[+Log Date], KANSAS.Type;

 

[Jeff Boyce]

See comments in-line below...

I am tryimng to run tjhis query and it is not doing what I want it to do.
I
have types and I want a count by type for the month of jan, feb, mar, etc.
so I would expect to see jan with 5 types amd a count of each. I hope
that
makes sense. Can you tell what is the matter? thanks

also the date came over in the import as 2/3/2008 5:11:28 PM so in design
view on the format line I did a mmm dd to get Mar 08.

You can change the appearance using format, but that doesn't change the
data. When you group by the date field you are actually grouping by each
and every date/time value in the field. Try using a query to return ONLY
the mmm dd (as text), then build a second query to group by that new field.

SELECT Count(KANSAS.[+Log Date]) AS [CountOf+Log Date], KANSAS.[+Log
Date],
KANSAS.Type
FROM KANSAS
GROUP BY KANSAS.[+Log Date], KANSAS.Type;

 

[JRS]

if I use this in my query....how do I get it to sort chronolically as april
is displayng and then feb/ thanks

Expr1: Format([Log Date],"mmm yy")

See comments in-line below...

I am tryimng to run tjhis query and it is not doing what I want it to do.
I
have types and I want a count by type for the month of jan, feb, mar, etc.
so I would expect to see jan with 5 types amd a count of each. I hope
that
makes sense. Can you tell what is the matter? thanks

also the date came over in the import as 2/3/2008 5:11:28 PM so in design
view on the format line I did a mmm dd to get Mar 08.

You can change the appearance using format, but that doesn't change the
data. When you group by the date field you are actually grouping by each
and every date/time value in the field. Try using a query to return ONLY
the mmm dd (as text), then build a second query to group by that new field.

SELECT Count(KANSAS.[+Log Date]) AS [CountOf+Log Date], KANSAS.[+Log
Date],
KANSAS.Type
FROM KANSAS
GROUP BY KANSAS.[+Log Date], KANSAS.Type;

 

[John Spencer]

SELECT Count(KANSAS.[+Log Date]) AS [CountOf+Log Date]
, Year(KANSAS.[+Log Date]) as YearNum
, Month(KANSAS.[+Log Date]) as MonthNum
, Format(KANSAS.[+Log Date],"mmm yyyy") as MonthAndYear
, KANSAS.Type
FROM KANSAS
GROUP BY Year(KANSAS.[+Log Date])
, Month(KANSAS.[+Log Date])
, Format(KANSAS.[+Log Date],"mmm yyyy")
, KANSAS.Type
ORDER BY Year(KANSAS.[+Log Date])
, Month(KANSAS.[+Log Date])
, KANSAS.Type

IF you don't want to display the MonthNum and YearNum remove them from
the select clause


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

if I use this in my query....how do I get it to sort chronolically as april
is displayng and then feb/ thanks

Expr1: Format([Log Date],"mmm yy")

See comments in-line below...

I am tryimng to run tjhis query and it is not doing what I want it to do.
I
have types and I want a count by type for the month of jan, feb, mar, etc.
so I would expect to see jan with 5 types amd a count of each. I hope
that
makes sense. Can you tell what is the matter? thanks

also the date came over in the import as 2/3/2008 5:11:28 PM so in design
view on the format line I did a mmm dd to get Mar 08.

You can change the appearance using format, but that doesn't change the
data. When you group by the date field you are actually grouping by each
and every date/time value in the field. Try using a query to return ONLY
the mmm dd (as text), then build a second query to group by that new field.

SELECT Count(KANSAS.[+Log Date]) AS [CountOf+Log Date], KANSAS.[+Log
Date],
KANSAS.Type
FROM KANSAS
GROUP BY KANSAS.[+Log Date], KANSAS.Type;

 

[JRS]

Great, it did just what I wanted when I ran the query but when I put it in a
pivot table I lost the monthly chronilogical order. Any way to fix that?
thanks

SELECT Count(KANSAS.[+Log Date]) AS [CountOf+Log Date]
, Year(KANSAS.[+Log Date]) as YearNum
, Month(KANSAS.[+Log Date]) as MonthNum
, Format(KANSAS.[+Log Date],"mmm yyyy") as MonthAndYear
, KANSAS.Type
FROM KANSAS
GROUP BY Year(KANSAS.[+Log Date])
, Month(KANSAS.[+Log Date])
, Format(KANSAS.[+Log Date],"mmm yyyy")
, KANSAS.Type
ORDER BY Year(KANSAS.[+Log Date])
, Month(KANSAS.[+Log Date])
, KANSAS.Type

IF you don't want to display the MonthNum and YearNum remove them from
the select clause


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

if I use this in my query....how do I get it to sort chronolically as april
is displayng and then feb/ thanks

Expr1: Format([Log Date],"mmm yy")

See comments in-line below...

I am tryimng to run tjhis query and it is not doing what I want it to do.
I
have types and I want a count by type for the month of jan, feb, mar, etc.
so I would expect to see jan with 5 types amd a count of each. I hope
that
makes sense. Can you tell what is the matter? thanks

also the date came over in the import as 2/3/2008 5:11:28 PM so in design
view on the format line I did a mmm dd to get Mar 08.
You can change the appearance using format, but that doesn't change the
data. When you group by the date field you are actually grouping by each
and every date/time value in the field. Try using a query to return ONLY
the mmm dd (as text), then build a second query to group by that new field.


SELECT Count(KANSAS.[+Log Date]) AS [CountOf+Log Date], KANSAS.[+Log
Date],
KANSAS.Type
FROM KANSAS
GROUP BY KANSAS.[+Log Date], KANSAS.Type;

 

[John Spencer]

The only way I know is to pivot on the Format of +Log Date as yyyy-mm

Format([+Log Date],"yyyy-mm")

You will lose the month names. If you are run the query on exactly one
year, you can use month names and get them in order by specifying the
column names in the Pivot clause of the query.

Pivot Format([+Log Date],"mmm") in ("Jan","Feb",...,"Nov","Dec")



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Great, it did just what I wanted when I ran the query but when I put it in a
pivot table I lost the monthly chronilogical order. Any way to fix that?
thanks

SELECT Count(KANSAS.[+Log Date]) AS [CountOf+Log Date]
, Year(KANSAS.[+Log Date]) as YearNum
, Month(KANSAS.[+Log Date]) as MonthNum
, Format(KANSAS.[+Log Date],"mmm yyyy") as MonthAndYear
, KANSAS.Type
FROM KANSAS
GROUP BY Year(KANSAS.[+Log Date])
, Month(KANSAS.[+Log Date])
, Format(KANSAS.[+Log Date],"mmm yyyy")
, KANSAS.Type
ORDER BY Year(KANSAS.[+Log Date])
, Month(KANSAS.[+Log Date])
, KANSAS.Type

IF you don't want to display the MonthNum and YearNum remove them from
the select clause


'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

if I use this in my query....how do I get it to sort chronolically as april
is displayng and then feb/ thanks

Expr1: Format([Log Date],"mmm yy")



:

See comments in-line below...

I am tryimng to run tjhis query and it is not doing what I want it to do.
I
have types and I want a count by type for the month of jan, feb, mar, etc.
so I would expect to see jan with 5 types amd a count of each. I hope
that
makes sense. Can you tell what is the matter? thanks

also the date came over in the import as 2/3/2008 5:11:28 PM so in design
view on the format line I did a mmm dd to get Mar 08.
You can change the appearance using format, but that doesn't change the
data. When you group by the date field you are actually grouping by each
and every date/time value in the field. Try using a query to return ONLY
the mmm dd (as text), then build a second query to group by that new field.

SELECT Count(KANSAS.[+Log Date]) AS [CountOf+Log Date], KANSAS.[+Log
Date],
KANSAS.Type
FROM KANSAS
GROUP BY KANSAS.[+Log Date], KANSAS.Type;