Getting counts

[MadCatCool]

Hello, I have a continuation of an earlier question. I have a chart that
lists top salespeople for every month going back to 2003. If I want to get a
chart of how many times each sales person’s name appeared in each month, how
would I do that?

I’ve created a chart in Excel that has columns of countifs. Countifs month
= jan AND name =tom, the cells below have the same formula except there is
one for each name; the column next to it is a copy of that column except the
month is feb and the column next to that is the same except it’s march.
Since we can’t use countifs in access, how would I do that? I don’t even
know if it would be a form or a query or report or just a control?

Any help would be appreciated. Thanks.

 

[John W. Vinson]

Hello, I have a continuation of an earlier question. I have a chart that
lists top salespeople for every month going back to 2003. If I want to get a
chart of how many times each sales person’s name appeared in each month, how
would I do that?

I’ve created a chart in Excel that has columns of countifs. Countifs month
= jan AND name =tom, the cells below have the same formula except there is
one for each name; the column next to it is a copy of that column except the
month is feb and the column next to that is the same except it’s march.
Since we can’t use countifs in access, how would I do that? I don’t even
know if it would be a form or a query or report or just a control?

Any help would be appreciated. Thanks.

I think you would need a Crosstab Query, but since you haven't posted a
description of your table structure it's a bit hard to be specific.

 

[MadCatCool]

I'm not sure how to further describe the table, it's just got columns for
month year and salesperson's name. I used to have the month in year in one
column as a date, but I found it to confusing to pick out names for all
instances of Jan without having to specify ranges for each year so I
separated the dates into month and year columns.

 

[John W. Vinson]

I'm not sure how to further describe the table, it's just got columns for
month year and salesperson's name. I used to have the month in year in one
column as a date, but I found it to confusing to pick out names for all
instances of Jan without having to specify ranges for each year so I
separated the dates into month and year columns.

Well... a date field would in fact be simpler here, because now you'll need to
put the month and year back together! What's the datatype of these two fields?
And what do they contain: is July stored as 7 or as "7" or as "July"?

 

[MadCatCool]

I still have the old table where they were stored as a date if that's really
easier.

 

[John W. Vinson]

I still have the old table where they were stored as a date if that's really
easier.

I'd suggest trying a crosstab query using a calculated field as the column
header: Format([datefield], "yyyy mm")

Alternatively, use a different calculated field:

FirstOfMonth: DateSerial(Year([datefield]), Month([datefield]), 1)

and set the format of the report control to mmmm, yyyy to display September,
2008 (or whatever format you prefer).