Grouping and sorting dates

[john johndon]

I have a table that has a field of retirement dates. People retire on the
last day of the month of their retirement age. So the day part of the date
field is irrelevant. So I would like to group by the month and year i.e. Oct
2004, November 2004, Dec 2004 etc through to the last month in the table. I
want to be able to count how many people will retire in each month.

I can't seem to figure this one out....thanks for your assistance.

JJ

 

[Wayne Morgan]

SELECT Year([RetDate]) AS RetYear, Month([RetDate]) AS RetMonth, Count(Table2.PersonID) AS
CountOfPersonID
FROM Table2
GROUP BY Year([RetDate]), Month([RetDate]);

 

[john johndon]

Thanks Wayne..

Can this be done on the query grid or only as SQL?

JJ

SELECT Year([RetDate]) AS RetYear, Month([RetDate]) AS RetMonth, Count(Table2.PersonID) AS
CountOfPersonID
FROM Table2
GROUP BY Year([RetDate]), Month([RetDate]);

--
Wayne Morgan
Microsoft Access MVP

I have a table that has a field of retirement dates. People retire on the
last day of the month of their retirement age. So the day part of the date
field is irrelevant. So I would like to group by the month and year i.e. Oct
2004, November 2004, Dec 2004 etc through to the last month in the table. I
want to be able to count how many people will retire in each month.

I can't seem to figure this one out....thanks for your assistance.

JJ

 

[Wayne Morgan]

Yes, it can be done on the query grid. It's just that the SQL is easier to post. If you do
the SQL then change to design view, you'll see what it looks like in the query grid. In
the query grid this looked like:

Turn on the Total row by clicking the Summation button on the tool bar (the sigma symbol).

Field Row
RetYear: Year([RetDate]) RetMonth: Month([RetDate]) CountOfPersonID: PersonID

Total Row
Group By Group By Count

Show Row
All 3 checked

--
Wayne Morgan
Microsoft Access MVP

Thanks Wayne..

Can this be done on the query grid or only as SQL?

JJ

SELECT Year([RetDate]) AS RetYear, Month([RetDate]) AS RetMonth, Count(Table2.PersonID) AS
CountOfPersonID
FROM Table2
GROUP BY Year([RetDate]), Month([RetDate]);

--
Wayne Morgan
Microsoft Access MVP

I have a table that has a field of retirement dates. People retire on the
last day of the month of their retirement age. So the day part of the date
field is irrelevant. So I would like to group by the month and year i.e. Oct
2004, November 2004, Dec 2004 etc through to the last month in the table. I
want to be able to count how many people will retire in each month.

I can't seem to figure this one out....thanks for your assistance.

JJ

 

[john johndon]

Thanks again wayne...

JJ

Yes, it can be done on the query grid. It's just that the SQL is easier to post. If you do
the SQL then change to design view, you'll see what it looks like in the query grid. In
the query grid this looked like:

Turn on the Total row by clicking the Summation button on the tool bar (the sigma symbol).

Field Row
RetYear: Year([RetDate]) RetMonth: Month([RetDate]) CountOfPersonID: PersonID

Total Row
Group By Group By Count

Show Row
All 3 checked

--
Wayne Morgan
Microsoft Access MVP

Thanks Wayne..

Can this be done on the query grid or only as SQL?

JJ

SELECT Year([RetDate]) AS RetYear, Month([RetDate]) AS RetMonth, Count(Table2.PersonID) AS
CountOfPersonID
FROM Table2
GROUP BY Year([RetDate]), Month([RetDate]);

--
Wayne Morgan
Microsoft Access MVP


I have a table that has a field of retirement dates. People retire

on
the

last day of the month of their retirement age. So the day part of

the
date

field is irrelevant. So I would like to group by the month and year

i.e.
Oct

2004, November 2004, Dec 2004 etc through to the last month in the table. I
want to be able to count how many people will retire in each month.

I can't seem to figure this one out....thanks for your assistance.

JJ