count off

J

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;
 
J

Jeff Boyce

See comments in-line below...

JRS said:
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.
Click to expand...

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;
Click to expand...
 
J

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")



Jeff Boyce said:
See comments in-line below...

JRS said:
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.
Click to expand...

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;
Click to expand...
Click to expand...
 
J

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")



Jeff Boyce said:
See comments in-line below...

JRS said:
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.
Click to expand...
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;
Click to expand...
Click to expand...
Click to expand...
 
J

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

John Spencer said:
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")



Jeff Boyce said:
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;
Click to expand...
Click to expand...
Click to expand...
 
J

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

John Spencer said:
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;
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Group by Range in Crosstab Query 4
DCount expression 10
Discreet Count of Data 4
Cross tab to begin fiscal year 11
Query advise please 1
CrossTab Query Format 2
Crosstab returns blank fields 6
Crosstab By Month with only last 6 months data 6

Top