Count number of entries for each month

[MadCatCool]

Hi there, I have a database that has entries going weekly from 2002 to
present. I want to count the number of times a given person's name comes up
by month, so that would be How many times did Jim's name come up in Jan (but
for all Januarys)? How many times did Joe's name come up in Jan? Then again
the same for each month. does anybody have any idea how I would do that?

I tried a query and asked for Jim when date -= Jan. However, it's in a
standard date format so the month day and year are not separated. Thanks for
any help!

 

[Dale Fye]

A little more info about your table structure would be helpful, but I'll
take a stab at it.

Depending on how you want this displayed, you could do something like:

SELECT [NameField], Month([DateField]), Format([DateField], "mmm"),
Count([ID]) as CountOfID
FROM yourTable
GROUP BY [NameField], Month([DateField]), Format([DateField], "mmm")
ORDER BY [NameField], Month([DateField])

This would give you a list of all of the Names, the appropriate months (but
would not give you the months where there were no records for a particular
name), the three letter month, and the count of the # of records for that
name, in that month (regardless of year). Another way to do this, which
would result in a single row for each name, and columns for each of the
months (like a spreadsheet) would be to use a crosstab query. If you create
the crosstab query, drag the [NameField], [DateField], and [ID] values into
the query grid. In the crosstab row, select "Row Heading" for the
[NameField], "Column Heading" for the formatted date field, and "Value" for
the [ID] field (also indicate that this is a Count, not a GroupBy, in the
Totals row of the grid. When you are done with that , run the query. You
should get what you are looking for, but the columns will be sorted
alphabetically. To get it in the right order, go into the SQL view and add
the final IN clause from the row below that starts with PIVOT. The way you
actually do this in your query is to right click in the area above the query
grid, select properties, and then add the column headers. These must match
exactly what show up in the query or you will end up with columns that are
NULL.

TRANSFORM Count(tbl_NameDates.ID) AS CountOfID
SELECT tbl_NameDates.NameField
FROM tbl_NameDates
GROUP BY tbl_NameDates.NameField
PIVOT Format([DateField],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

HTH
Dale

 

[MadCatCool]

Wow, that's really technical! I wish Access was a little easier. However, I
love playing with it so I'm going to spend some time trying it all out. You
really take helping people out seriously, don't you? Thanks so much!

A little more info about your table structure would be helpful, but I'll
take a stab at it.

Depending on how you want this displayed, you could do something like:

SELECT [NameField], Month([DateField]), Format([DateField], "mmm"),
Count([ID]) as CountOfID
FROM yourTable
GROUP BY [NameField], Month([DateField]), Format([DateField], "mmm")
ORDER BY [NameField], Month([DateField])

This would give you a list of all of the Names, the appropriate months (but
would not give you the months where there were no records for a particular
name), the three letter month, and the count of the # of records for that
name, in that month (regardless of year). Another way to do this, which
would result in a single row for each name, and columns for each of the
months (like a spreadsheet) would be to use a crosstab query. If you create
the crosstab query, drag the [NameField], [DateField], and [ID] values into
the query grid. In the crosstab row, select "Row Heading" for the
[NameField], "Column Heading" for the formatted date field, and "Value" for
the [ID] field (also indicate that this is a Count, not a GroupBy, in the
Totals row of the grid. When you are done with that , run the query. You
should get what you are looking for, but the columns will be sorted
alphabetically. To get it in the right order, go into the SQL view and add
the final IN clause from the row below that starts with PIVOT. The way you
actually do this in your query is to right click in the area above the query
grid, select properties, and then add the column headers. These must match
exactly what show up in the query or you will end up with columns that are
NULL.

TRANSFORM Count(tbl_NameDates.ID) AS CountOfID
SELECT tbl_NameDates.NameField
FROM tbl_NameDates
GROUP BY tbl_NameDates.NameField
PIVOT Format([DateField],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

HTH
Dale

Hi there, I have a database that has entries going weekly from 2002 to
present. I want to count the number of times a given person's name comes
up
by month, so that would be How many times did Jim's name come up in Jan
(but
for all Januarys)? How many times did Joe's name come up in Jan? Then
again
the same for each month. does anybody have any idea how I would do that?

I tried a query and asked for Jim when date -= Jan. However, it's in a
standard date format so the month day and year are not separated. Thanks
for
any help!

 

[Dale Fye]

Glad to help.

Wow, that's really technical! I wish Access was a little easier.
However, I
love playing with it so I'm going to spend some time trying it all out.
You
really take helping people out seriously, don't you? Thanks so much!

A little more info about your table structure would be helpful, but I'll
take a stab at it.

Depending on how you want this displayed, you could do something like:

SELECT [NameField], Month([DateField]), Format([DateField], "mmm"),
Count([ID]) as CountOfID
FROM yourTable
GROUP BY [NameField], Month([DateField]), Format([DateField], "mmm")
ORDER BY [NameField], Month([DateField])

This would give you a list of all of the Names, the appropriate months
(but
would not give you the months where there were no records for a
particular
name), the three letter month, and the count of the # of records for that
name, in that month (regardless of year). Another way to do this, which
would result in a single row for each name, and columns for each of the
months (like a spreadsheet) would be to use a crosstab query. If you
create
the crosstab query, drag the [NameField], [DateField], and [ID] values
into
the query grid. In the crosstab row, select "Row Heading" for the
[NameField], "Column Heading" for the formatted date field, and "Value"
for
the [ID] field (also indicate that this is a Count, not a GroupBy, in the
Totals row of the grid. When you are done with that , run the query.
You
should get what you are looking for, but the columns will be sorted
alphabetically. To get it in the right order, go into the SQL view and
add
the final IN clause from the row below that starts with PIVOT. The way
you
actually do this in your query is to right click in the area above the
query
grid, select properties, and then add the column headers. These must
match
exactly what show up in the query or you will end up with columns that
are
NULL.

TRANSFORM Count(tbl_NameDates.ID) AS CountOfID
SELECT tbl_NameDates.NameField
FROM tbl_NameDates
GROUP BY tbl_NameDates.NameField
PIVOT Format([DateField],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

HTH
Dale

Hi there, I have a database that has entries going weekly from 2002 to
present. I want to count the number of times a given person's name
comes
up
by month, so that would be How many times did Jim's name come up in Jan
(but
for all Januarys)? How many times did Joe's name come up in Jan? Then
again
the same for each month. does anybody have any idea how I would do
that?

I tried a query and asked for Jim when date -= Jan. However, it's in a
standard date format so the month day and year are not separated.
Thanks
for
any help!