Monthly count

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 


0
Ramesh
4/16/2007 10:03:31 AM
access 16762 articles. 3 followers. Follow

3 Replies
571 Views

Similar Articles

[PageSpeed] 55

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ramesh" <ramesh2020@gmaildotcom> wrote in message
news:udTMK6AgHHA.4704@TK2MSFTNGP06.phx.gbl...
> 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 

0
Allen
4/16/2007 10:19:57 AM
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
"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message 
news:%23nTKLDBgHHA.4868@TK2MSFTNGP06.phx.gbl...
> 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
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Ramesh" <ramesh2020@gmaildotcom> wrote in message
> news:udTMK6AgHHA.4704@TK2MSFTNGP06.phx.gbl...
>> 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
> 


0
Ramesh
4/16/2007 10:34:57 AM
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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ramesh" <ramesh2020@gmaildotcom> wrote in message
news:uU62uLBgHHA.1220@TK2MSFTNGP03.phx.gbl...
> 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
> "Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message 
> news:%23nTKLDBgHHA.4868@TK2MSFTNGP06.phx.gbl...
>> 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" <ramesh2020@gmaildotcom> wrote in message
>> news:udTMK6AgHHA.4704@TK2MSFTNGP06.phx.gbl...
>>>
>>> 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 

0
Allen
4/16/2007 11:10:49 AM
Reply:

Similar Artilces:

Count Number Of Employees for Month and Year historically
how i would i get the number of employees for each month and year that we have data for? my fields are : Representative_Name Approval_Date Termination_Date my data goes back to 06/2007. i can get how many person's were hired/fired in a particular month/year in history but a total number of un-terminated persons up to that month in history is eluding me. hope that makes sense. AC2007/XP. It depends on how you define 'the number of employees for each month'. Do you measure at the start of the month or the end? What do you want to do with an employee who ...

Crosstab column data count
I have a crosstab query with Well Name as row heading , site visit Dates as column headings (formatted as "yyyy"), and averaged water level measurements data as the values. The column headings range from 2004-2010 and some wells have no measurements during some of these years. I need to create a dataset from the crosstab that shows total number of years for which each well has data. My attempts at changing the column headings and values around to accomplish this have been futile. Please help. Post your crosstab SQL. -- Build a little, test a little. "tmb...

Counting with numerous citeria..complicated
Hi, I have a complicated question for help. I hope that I make it sound ok. First thing to remember is that this sheet is linked to another, which is in-turn link to another...ive been filtering data! so what i have is: >From B3:B200 a list of brands >From column F3 to to Z3 I have a list of shops and then their respective sales in F3:Z200 What I want is for it to idenitfy where there are only sales in one store (so where there are it will say one)...that is easy I can do that! but the next bit is where I am stuck. The brands can be the same, so there could be more than 1 brand...

Intuit iPhone-friendly Quicken priced at $3/month
http://snipurl.com/1vk5m "Quicken's U.S. market share rose to 80 percent in the first 11 months of this year from 72 percent in 2006. Microsoft Money's share of sales declined to 19 percent from 25 percent in the prior year, according to NPD." "Brent" <undefined> wrote in message news:OTqBzwoQIHA.1184@TK2MSFTNGP04.phx.gbl... > http://snipurl.com/1vk5m It is not hard to believe. It's also hard to see that MS is doing anything, or cares to do anything, to arrest the slide. "Brent" <undefined> wrote in message news:u0XTH4o...

Count missing data
Hello, I would like to count missing data in a Column range due to the criteria in another column range. The problem I have is that the "Birth Date" column range has a "vlookup" formula in it, which sometimes formulates a Blank. I need to count these Blank cells when the Status = "A". Example: Birth Date Status 02/03/71 A A 12/02/58 A 09/15/67 A A 11/21/79 A Missing = 2 Thanks Ruan Try this: =SUMPRODUCT((A2:A100={" ",""})*(B2:B100="a")) -- HTH, RD...

Not to inlcude zero in COUNT
Hi Please can you help with this problem. I need a count in a column with amounts. The problem being I do require to count the Credits and Debits, but NOT the 0 (ZEROS). I do have 3 criteria that this applies to. eg If Cols B, C and D meet the criteria count any activity in N. Thanking you Anne Try =SUMPRODUCT((B1:B100=criteria1)*(C1:C100=criteria2)* (D1:D100=criteria3)*(ISNUMBER(N1:N100))*(N1:N100<>0)) With criterias in J1, J2, J3 try the below which will count the number of values in N1:N100 except zeroes and blanks =SUMPRODUCT((B1:B100=J1)*(C1:C100=J2)*(D1...

Label a chart of counts with other percentage data
Here's the data: Group 2005 2006 2007 LTM Data A 19.4% 22.8% 21.2% 19.9% Profitability A 6 7 7 7 Count B 9.5% 31.6% 30.4% 30.7% Profitability B 2 3 3 3 Count C 22.4% 23.6% 16.6% 17.6% Profitability C 15 16 17 18 Count D 19.2% 20.5% 15.9% 13.7% Profitability D 8 8 9 10 Count I have successfully generated a stacked bar chart that shows the counts per group by year. Now I would like to include a label for each group to show profitability for each group in each year in the 4 stacks. How would I do that? Thanks, --...

Month and Year Only
Is there any way to enter just the month and year without the day? For instance, if I enter 4/6 (for April 2006) it defaults to April 6, 2004. I would like for it to read it as April 2006. Thank you. As far as I know, there is no way to enter only month and year. You ca however make it look the way you want on the sheet through th formatting process. First, fortmat the cells, using custom format, to mmmm yyyy. Then, yo will either have to enter 4/1/6 or 4/2006. Either way, the actua entry will be April 1, 2006. It will appear as "April 2006" because o the formatting -- Mess...

How to view credit card balance by month?
How can I view a credit card balance by month so I can see how much it has gone up or done during some period? Thanks, Brett In microsoft.public.money, Brett wrote: >How can I view a credit card balance by month so I can see how much it has >gone up or done during some period? You could customize the WhatIHave->NetWorthOverTime to include just your credit card account(s). "Cal Learner-- MVP" <via_newsgroup@please.tnx> wrote in message news:vvu9k015iccau0sjjkn5rh8i92mbe2r4qe@4ax.com... > In microsoft.public.money, Brett wrote: > >>How can I view...

Counting records with Values
I'm basically a beginner in Access, but trying to become more proficient. Generally I pull data into Excel to manipulate, but I've run up against too big a database. I'm trying to count all of the records that have a value of greater than 0. I'm pulling from another query that has multiple records. The details are: Multiple months of sales information (this is where I'm trying to count >0). Multiple franchise locations and multiple SKUs. I'm trying to pull how many franchise locations Sales of >0 units for each SKU. I'm good with the SELECT piece, b...

Simple Count of certain cells
Whats the simplest way of counting how many times certain letters appear in a list? In cells A1:A2000, I have Ethnicity codes with singular letters A-Z, I would like to count cetrain groups of letters say A,C,G,P,Z. Thanks =COUNTIF(A1:A2000,"A") -- David Biddulph "JPDS" <JPDS@discussions.microsoft.com> wrote in message news:B782FA55-E4AD-4680-B426-10FCF2E0D3FD@microsoft.com... > Whats the simplest way of counting how many times certain letters appear > in a > list? > In cells A1:A2000, I have Ethnicity codes with singular letters A-Z,...

Is there a quick way to count how many emails are in my .PST file(s)
Hi Is there a quick way to count how many emails are in my .PST file? (Why? Because I want to know whether GoogleDesktop has managed to spider my archive file properly...) Ship Shiperton Henethe IN OUTLOOK? - Click on the highest level folder Left-click and select properties There should be a button there called "Folder Size" Click it..... "ship" <shiphen@gmail.com> wrote in message news:1161969600.785376.108820@k70g2000cwa.googlegroups.com... > > Hi > > Is there a quick way to count how many emails are in my .PST file? > > (Why? Because ...

Counting consecutive cells
Hi, I have a table with 60 columns, each column marked A, B or X. I need to know how many times X's appear consecutively. How do I do this? Thanks in advance Neil Hi and what should happen if you have more than one series of 'X'? Maybe give an example + your expected result "neil" wrote: > Hi, > > I have a table with 60 columns, each column marked A, B or X. I need to know > how many times X's appear consecutively. How do I do this? > > Thanks in advance > > Neil Hi Frank, Yes, that is what I want. (COLUMNS) A B ...

Enrol Date counts
I have a student database with an Enrolled date. I would like to create a form where it displays how many students enrolled each week. I would like to have text boxes that display the student count for each program in this manner: Music Dance Art Drama Jan 1, 2007 - Jan 7, 2007 7 5 1 0 Jan 8, 2007 - Jan 15, 2007 1 2 2 1 Jan 16, 2007 - Jan 23, 2007 2 0 1 2 ect. is this or some thing similar possible best regards For ...

Get Month and Year from death field
I havedate/time field that stores value like 2010-4-10 00:00:00 I need to get the month and year like Apr-2010 or April-2010 I tried... (DATEPART(MONTH,WRKODR.DateDel) + DATEPART(Year, WRKODR.DateDel)) As MTYr It returns 2010 in some cases 2011... I think it just add month to year... I need help Thanks in advance After more googling I tried (DateName(Month, WRKODR.DateDel) + ' ' + DateName(Year, WRKODR.DateDel)) As MTYr It pulls date and month correctly as in April 2010 Any "inherent danger" in using datename? "jpBless&q...

staff monthly scheduling problem
I have recently been given an added responsibility at work that require me to prepare a schedule for a staff of 10, some part time, some full. I have been racking my brain for a month and trying every trick o formula I could to develop a monthly calendar with set shifts (5/day for unchanging employees that would calculate hours for each employe by week and by month. I would like to be able to program something fo example: Gary works Tues-Sat 6:30AM-3:00PM, with a 30 minute lunch. The lunc period does not need to be shown on the calendar since this woul ideally be a hidden formula only neces...

Counting Blank Cells
H16 I16 J16 $25.2 $33.8 The following formula counts the number of dollar values in H, I and J. =IF(H16="","",IF(M16>0,COUNT(H16:J16))) [answer =2] Without putting $0.00 in cell J16 how would I write a formula to count all three cells? [answer = 3] It depends on the requirement. Will J16 ever contain an actual value of 0? Are the values always positive? Do you want to count J16 even if H and I are empty? >=IF(H16="","",IF(M16>0,COUNT(H16:J16))) You don't have a value_if_false argument de...

Customer record count query
Hi everbody - I am struggling here - I need a query showing the number of customers that I have and also a record count as well. The result should look like this for eg> name address city state phone mary 555 main st miami florida 2015551212 henry 896 mott st orlando florida 201777888 total records = 2 select custname, address1, city, state, phone from RM00101 Compute count(*) -- Charles Allen, MVP "Michael@nyresume.com" wrote: > Hi everbody - > I am struggling here - I need a query showing the number of customers that I > h...

shading certain months on time chart
I have several years of data - the x-axis is a time-scale. I'd like to have the winter months of the year shaded - to make it easier to see the seasonality in the data. Is there a way to pick a period each year (e.g. Nov. 15 - March 15) which will be shaded? The easiest way to do this is to have a series of data for the winter months only. That way you can shade that series differently. "Bob Richardson" <bobr at whidbey dot com> wrote in message news:5-2dnZt2r8bRxYzfRVn-2w@whidbeytel.com... > I have several years of data - the x-axis is a time-scale. I'd li...

Monthly Calendar Report
I have a db with a table that has employee names and dates scheduled for the year. Could someone please direct me on how to create a report (or even an examples of reports) that shows a monthly calendar report for the year with the employee names for each day. There can be several employees of for one day and I need for all of the names to show under that date. Thanks in advance for any help you can provide. Dennis wrote: >I have a db with a table that has employee names and dates scheduled for the >year. Could someone please direct me on how to create a report (or even an &...

How do you sort a date range by month?
We are trying to find out how many birthdays fall with in a given month using excel. You could use a helper column, with something like = A2. Format this (Format - Cells - Number - Custom: mmm. Now you can use Data - Subtotals, "At each change in: Month" and "Use function: Count." -- Earl Kiosterud www.smokeylake.com/ ------------------------------------------- "Brewisc13" <Brewisc13@discussions.microsoft.com> wrote in message news:44E02AAC-8216-43F5-846F-E981E978E44B@microsoft.com... > We are trying to find out how many birthdays fall with in...

How can I count unique values in a query in the report footer 12-16-07
I have a report that gives me the count of the status of individuals . This works fine as long is there is only one record in the query (in my query there is one record per month). When I query 12 months (individuals may appear in various months) it counts each record of an individual. For Example in a query considering 12 months for a widow Jones it may count her 12 times and for a survivor named Smith may count her 8 times: Widows 12 Survivors 8 I would like to add a count in the report footer that will tell me how many unique individuals I have in the report (Example widows: Tot...

SUM of COUNT?
I have a report based on a crosstab query that counts the number of enquiries from an advertising source for a year and sorts them into months So columns are by date and rows give the source and the value is the count of enquries in the source. I want to total the number of enquiries in the month on the bottom of the report. I have tried putting a text box in the report footer that uses the value field and set it's running sum property to over all but it just gives the sum for the source and not all the sources added together. Is it possible to do this? Peter One of the f...

how do I count cells with a value >0 in multiple worksheets
"Jet" wrote: > When using: =COUNTIF(Sheet1:Sheet3!B1,">0") I get a "Value" error B1 on sheet1 has value 1 B1 on sheet2 has value 1 B1 on sheet3 has no value I want the cel count to = 2 Hi COUNTIF can't work on multiple sheets Try the following formula instead: =SUMPRODUCT(COUNTIF(INDIRECT("'Sheet" & ROW(1:3) & "'!B1),">0")) -- Regards Frank Kabel Frankfurt, Germany "Jet" <Jet@discussions.microsoft.com> schrieb im Newsbeitrag news:72286B48-B504-43F9-A27B-26EACE1C8C68@microsoft.com... ...

Counting in intervals
How to count number of numbers in interval from A1 to A2, A2-A3, A3-A4? -- Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ I think you want to look at the FREQUENCY function. -- HTH RP (remove nothere from the email address if mailing direct) "John Smith" <jsmith@rt.com> wrote in message news:opslex90b0rwgaw6@novi... > > How to count number of numbers in interval from A1 to A2, A2-A3, A3-A4? > > -- > Using Opera's revolutionary e-mail client: http://www.opera.com/m2/ ...