Monthly count

[Ramesh]

HI,

I have a table with Product field and SaleDate field.

I need to make a query that will give me a count of number of times a
product was sold on a monthly basis. In other words, the number of records
for each product in a month. Something like each productname as
columnheading and name of the month as row heading, and the count in the
tabulation.

do i need to use crosstab query to do this? could someone guide me here?

Thanks very much.

Ramesh

 

[Allen Browne]

1. Create a query using your table(s).

2. Change it to a Crosstab query (Crosstab on Query menu.)

3. Type this expression into the Field row:
TheYear: Year([SaleDate])
In the Total row under this field, choose Group By.
In the Crosstab row, choose Row Heading.

4. In the next column, type this into the Field row:
TheMonth: Month([SaleDate])
In the Total row, choose Group By.
In the Crosstab row, choose Row Heading.

5. In the next column, choose your ProductName field.
In the Total row, choose Group By.
In the Crosstab row, choose Column Heading.

6. In the next column, choose your primary key field.
In the Total row, choose Count.
In the Crosstab row, choose Value.

 

[Ramesh]

Thanks Allen. I worked out almost the same thing after i posted my request.
But I was still stuck at 2 places. The major one was Getting the month of
each year. I was getting the total count of a particular month from all
years. Putting the year in a separate column as you suggested has done the
trick.

Second minor one is to get the name of the month in the display rather than
the numeral. Still figuring that one out.

Thanks a ton. Allen.

Ramesh

1. Create a query using your table(s).

2. Change it to a Crosstab query (Crosstab on Query menu.)

3. Type this expression into the Field row:
TheYear: Year([SaleDate])
In the Total row under this field, choose Group By.
In the Crosstab row, choose Row Heading.

4. In the next column, type this into the Field row:
TheMonth: Month([SaleDate])
In the Total row, choose Group By.
In the Crosstab row, choose Row Heading.

5. In the next column, choose your ProductName field.
In the Total row, choose Group By.
In the Crosstab row, choose Column Heading.

6. In the next column, choose your primary key field.
In the Total row, choose Count.
In the Crosstab row, choose Value.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

HI,

I have a table with Product field and SaleDate field.

I need to make a query that will give me a count of number of times a
product was sold on a monthly basis. In other words, the number of
records for each product in a month. Something like each productname as
columnheading and name of the month as row heading, and the count in the
tabulation.

do i need to use crosstab query to do this? could someone guide me here?

Thanks very much.

Ramesh

 

[Allen Browne]

If you reallly need the month name displayed in the query, add another
field:
Field: TheMonthName: Format([SaleDate], "mmmm")
Total: First
Crosstab: Row Heading

This means you still have the month number to sort on.
(If you just use the month name, April sorts first.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen. I worked out almost the same thing after i posted my
request. But I was still stuck at 2 places. The major one was Getting the
month of each year. I was getting the total count of a particular month
from all years. Putting the year in a separate column as you suggested
has done the trick.

Second minor one is to get the name of the month in the display rather
than the numeral. Still figuring that one out.

Thanks a ton. Allen.

Ramesh

1. Create a query using your table(s).

2. Change it to a Crosstab query (Crosstab on Query menu.)

3. Type this expression into the Field row:
TheYear: Year([SaleDate])
In the Total row under this field, choose Group By.
In the Crosstab row, choose Row Heading.

4. In the next column, type this into the Field row:
TheMonth: Month([SaleDate])
In the Total row, choose Group By.
In the Crosstab row, choose Row Heading.

5. In the next column, choose your ProductName field.
In the Total row, choose Group By.
In the Crosstab row, choose Column Heading.

6. In the next column, choose your primary key field.
In the Total row, choose Count.
In the Crosstab row, choose Value.

I have a table with Product field and SaleDate field.

I need to make a query that will give me a count of number of times a
product was sold on a monthly basis. In other words, the number of
records for each product in a month. Something like each productname as
columnheading and name of the month as row heading, and the count in the
tabulation.

do i need to use crosstab query to do this? could someone guide me
here?

Thanks very much.

Ramesh