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 said:
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!
