Simple way to count by month?

Access2000

Hi I have a table that is just a list of serial numbers and dates that they 
were sold.

Is there a simple way to build a query that will just list the total serials 
numbers by month, i.e.

Dec-07 - 566
Nov-07- 453
Oct-07- 343

Each serial is on its own row. Getting the number sold by day is simple, but 
grouping and suming these to months has me stumped.

Thanks - Steve 

0
Steve
1/8/2008 4:59:47 PM
access 16762 articles. 3 followers. Follow

3 Replies
624 Views

Similar Articles

[PageSpeed] 0

Solved it.

I used this if anybody else is looking

SELECT Format([SQL_DELIVERY_DATE],"yyyy mm") AS MonthSold, Count(*) AS Total
FROM tbl_sold
GROUP BY Format([SQL_DELIVERY_DATE],"yyyy mm")
ORDER BY Format([SQL_DELIVERY_DATE],"yyyy mm");


SQL_DELIVERY_DATE

"Steve B" <meinthecorner@gmail.com> wrote in message 
news:C32FF4A9-443D-4FB7-9E1B-80B815FCCFAD@microsoft.com...
> Access2000
>
> Hi I have a table that is just a list of serial numbers and dates that 
> they were sold.
>
> Is there a simple way to build a query that will just list the total 
> serials numbers by month, i.e.
>
> Dec-07 - 566
> Nov-07- 453
> Oct-07- 343
>
> Each serial is on its own row. Getting the number sold by day is simple, 
> but grouping and suming these to months has me stumped.
>
> Thanks - Steve 

0
Steve
1/8/2008 5:46:24 PM
On Tue, 8 Jan 2008 16:59:47 -0000, "Steve B" <meinthecorner@gmail.com> wrote:

>Access2000
>
>Hi I have a table that is just a list of serial numbers and dates that they 
>were sold.
>
>Is there a simple way to build a query that will just list the total serials 
>numbers by month, i.e.
>
>Dec-07 - 566
>Nov-07- 453
>Oct-07- 343
>
>Each serial is on its own row. Getting the number sold by day is simple, but 
>grouping and suming these to months has me stumped.
>
>Thanks - Steve 

You can use a calculated field in a Totals query. Assuming the fields are
named SerialNo and SaleDate, try

SELECT Format([saledate], "mmm-yy"), Count(*)
FROM yourtable
GROUP BY Year([saledate]), Month([saledate]), Format([saledate], "mmm-yy")
WHERE saledate BETWEEN [Enter start date:] AND [Enter end date:];



I'm using Format to convert the date to a string, but grouping by the Year and
Month rather than by the string, since the string will sort alphabetically
(April first) rather than chronologically.

             John W. Vinson [MVP]
0
John
1/8/2008 6:25:08 PM
Thanks John.

"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message 
news:ipf7o357v4v7p27sqk14fkcma7ae0ntm5a@4ax.com...
> On Tue, 8 Jan 2008 16:59:47 -0000, "Steve B" <meinthecorner@gmail.com> 
> wrote:
>
>>Access2000
>>
>>Hi I have a table that is just a list of serial numbers and dates that 
>>they
>>were sold.
>>
>>Is there a simple way to build a query that will just list the total 
>>serials
>>numbers by month, i.e.
>>
>>Dec-07 - 566
>>Nov-07- 453
>>Oct-07- 343
>>
>>Each serial is on its own row. Getting the number sold by day is simple, 
>>but
>>grouping and suming these to months has me stumped.
>>
>>Thanks - Steve
>
> You can use a calculated field in a Totals query. Assuming the fields are
> named SerialNo and SaleDate, try
>
> SELECT Format([saledate], "mmm-yy"), Count(*)
> FROM yourtable
> GROUP BY Year([saledate]), Month([saledate]), Format([saledate], "mmm-yy")
> WHERE saledate BETWEEN [Enter start date:] AND [Enter end date:];
>
>
>
> I'm using Format to convert the date to a string, but grouping by the Year 
> and
> Month rather than by the string, since the string will sort alphabetically
> (April first) rather than chronologically.
>
>             John W. Vinson [MVP] 

0
Steve
1/8/2008 7:12:11 PM
Reply:

Similar Artilces:

Record count based on status
I need to write a query that will yeild a report that counts the number of projects based on date fields. For example: a project might be in an upcoming status based on 1. first record date and 2. no engineer start date. If the first record date is 11/01/07 and the engineer start date is 2/1/08, the project is considered to be in upcoming status for November 2007, December 2007, and January 2008. It then is considered to be in design status for February 2008. Can anyone offer any tips on how to write this query? Thanks -KB More rules are needed. It looks like --- no engineer [...

Cycle Counting Serial Number Tracked Products
Hello, Hello, I messed up my first post hit the wrong key. I am looking for ideas on how to cycle count Serial Number Tracked Inventory without increasing workload. I would be interested in know how other companies handle cycle counting of serialized inventory items. Thank in adavnced. Brian Morris Database Administrator >-----Original Message----- >Hello, > > >. > Many customers utilize QualityCount, our Physical Inventory and Cycle Count program along with a handheld barcode scanner to facilitate serial and lot tracked physical inventory and cycle coun...

help please with simple formula
F4 i have 0 H4 i have 6 J4 i have 1.12 The answer in N4 is 6 (good enough but would like to round to nearest No which should be 7) Formula used is =H4-F4*J4 F7 i have 0 H7 i have 6 J7 i have 2.24 The answer in N7 is still 6 ( it should be 12, double the one above) Formula used is =H7-F7*J7 just can`t work this out. help please thanks Scudo wrote: > F4 i have 0 > H4 i have 6 > J4 i have 1.12 > The answer in N4 is 6 (good enough but would like to round to nearest No > which should be 7) > Formula used is =H4-F4*J4 > > F7 i have 0 > H7 i have 6 ...

Conditional formating for the month
I have a column with the birthday of some clients and I want the cell format to change to yellow when his date of birth is withing the current and the next monat. In order to do that I wrote these formula in the first and second field of the conditional formating panel=C2=MONTH(TODAY()) and =C2=MONTH(TODAY())+1 , but it doesn't work. Can't you tell me what am I doing wrong Thanks Try this, using only one condition: =OR(MONTH(C2)=MONTH(NOW()),MONTH(C2)=MONTH(NOW())+1) =OR(MONTH(C2)=MONTH(TODAY(),MONTH(C2)+1=MONTH(TODAY()) all in the one CF condition -- HTH RP (remove not...

Count Check Marks
I have a report that counts yes/no. I am using a between date range on it. When I use the following control; =Sum(Abs([ADC_DD-2656-7])) when printing the report I get multiple pages. Could someone assist? If I can format the report to show the Month and year and the totals, that would be Great! hi, lmossolle wrote: > I have a report that counts yes/no. I am using a between date range on it. > When I use the following control; > =Sum(Abs([ADC_DD-2656-7])) Why don't you count it in the record source (the query behind your report)? > when printing the report I get multipl...

standard filter "older than x months" limitations
Hi, anyone who knows how to work around the standard filter limitation of "older than x months"? You are only able to enter a number between 0 and 100! I need to have a saved query (=dynamic list) of contacts who are older than e.g. 30 years. I assumed that I could find this easily throught the standard methods, namely create a view for contacts with birthdate "older than x month" 1230 (=12*30) but apparently there's a restriction in this?! Anyone knows how to work around this with equal effort? I wonder why those limitations are built in?! Microsoft could at...

Help with Count Please
I need the correct formula to count the following A B 1 5 FW 2 3 G 3 4 G 4 3 R I'd like to count the "B" Column but not when "3" appears in the "A" column. The result of the above would be "2", since column "A" has a "3" in rows "2" and "4". Thanks in advance, Alan You haven't answered Dave's question. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Alan Bernardo" <ifeelyourpain@ihatebush.net> wrote...

Microsofts way of gainig PROFIT #2
I'm asking you following. When you are selling your product Windows XP you are=20 promising a lot of extras that is in the product. But when the user wants to use them it all of sudden costs=20 700 dollars to get information of how to set it up. Is it Microsofts policy to hide all the costs so that=20 first when the user is hooked on the Program. He will=20 start to pay. Now in Sweden it's like this, that it's illegal according=20 to Swedisch consumer law to suppress information like=20 this. In Sweden you can't tell that some option is=20 included in the price but not tellin...

Is there a simple way to move contacts?
Help!!! I am trying to move my "contacts" data base to a new computer and can't find a simple way to do so. I have tried calling MS but no luck finding help. Any help will be greatly appreciated. Not sure what version of Outlook you are using, but look for files with a *.PST and/or *.PAB extension. (Contacts are stored in the *.PST while the *.PAB is a legacy personal address book.) "Dale" <anonymous@discussions.microsoft.com> wrote in message news:55a301c47433$609e7cd0$a401280a@phx.gbl... > Help!!! > > I am trying to move my "contacts"...

Need a simple way to have only one mode per link with an option
I use items like the following to display links in an iFrame within my page. <asp:TreeNode NavigateUrl="http://en.wikipedia.org/wiki/Church" Target="LinksFrame" Text="Wikipedi Item on Church"></asp:TreeNode> I can envision situations where the iFrame might be too small and the user would like to display in another window. I'd like to have a check box to allow that option. On way I could implement is to have two statements for each link and use the check box to select the appropriate one. Is there a simple way to have only o...

Simple question, how to do XPath search by XmlDocument with namesp
For a XML fragment like below <ns0:BusCar_Request xmlns:ns0="http://BTSDG_SQL"> <ns0:sync> <ns0:after> <ns0:BusinessCards NameOnCard="NameOnCard_1" TitleOnCard="TitleOnCard_1" Quantity="10" /> <ns0:BusinessCards NameOnCard="NameOnCard_2" TitleOnCard="TitleOnCard_2" Quantity="10" /> <ns0:BusinessCards NameOnCard="NameOnCard_3" TitleOnCard="TitleOnCard_3" Quantity="10" /> </ns0:after> </ns0:sync> </ns0:BusCar_Reque...

Simple hack to get $800 to your home. 06-07-10
Simple hack to get $800 to your home at http://mastidunia.tk Due to high security risks,i have hidden the cheque link in an image. in that website on left side below search box, click on image and enter your name and address where you want to receive your cheque.please dont tell to anyone. ...

How do I count the number of records within a date range
How do I count the number of records within a date range. eg 1/1/2003,1/5/2003,1/10/2004 Using countif between 1/1/2003 and 31/12/2003 = 2 =COUNTIF(A1:A100,">="&"1/1/2003")-COUNTIF(A1:A100,">"&"12/31/2003") Format cell as General "Mike" wrote: > How do I count the number of records within a date range. > eg 1/1/2003,1/5/2003,1/10/2004 Using countif between 1/1/2003 and 31/12/2003 > = 2 try =sumproduct((year(daterng)=2003)*1) -- Don Guillett SalesAid Software dguillett1@austin.rr.com "Mike" <Mike@dis...

Simple user defined function
Hi Please can anyone help I have created a Function CtoF(Centigrade) CtoF = Centigrade * 9 / 5 + 32 in theVB sheet1 code I have then placed the function = CtoF (A2) in the worksheet cell B2 A2 contains a number, the function in Cell B2 should convert the number in A2 to degrees Farenheit but it only gives me an error #NAME? I have the correct addins and checked the names of functions but nothing works please someone help. Hi there have you stored this UDF. It has to go into a standard module -- Regards Frank Kabel Frankfurt, Germany "Subs" <atof31@dsl.pipex.com> s...

Counting Filtered Results Using Autofilter
I frequently use Autofilter to count the number of instances in a column of data. Usually, when I apply the autofilter to the column heading and execute a search, the number of results appears in a bar at the lower left hand corner of the Excel window, similar to "127 of 658 items". Periodically, this changes to "Ready" and no longer returns the number of instances of the filtered results. Any idea why this happens, how can I prevent it, and how do I make it starting counting again? Thanks John You sure you're seeing Ready? Debra Dalgleish exp...

Easy way to create a bunch of worksheets and name them
Let's say I have a workbook and want to create 60 or so worksheets. But I don't want to go in a name them after I create them. What if I had a list of names...like in a column. Then I wanted to run a macro that would create a worksheet for each name in the list and name it. What would the code look like? Frank it might look something like this: Sub InsertSheets() Dim Cell As Range Dim NewSheet As Worksheet For Each Cell In Range("A1:A" & Range("A65536").End(xlUp).Row) Set NewSheet = _ Sheets.Add(After:=Sheets(Worksheets.Count)) NewSheet.Name =...

Count occurrences
Hello all, I am very new to Access, am doing an online course and learning as I go. I am stuck with one query and I'm hoping someone can help me. I have a sales table for which I am required to produce a query which tells the most popular artists and the number of sales they have made. The fields in the table are:- Invoice number, product code, artist code, date, payment type. The query is on the Artist Code field, but for the life of me I cannot get it to show just the top 5 Artists and their sales count. Hoping for help - written slowly 'cos I'm very new at this. ...

Counting Text Entries
Hi everyone, I have a large database of information which includes columns of text entries. Is there a way of counting these text entries? e.g.: HBO SL SL MN RTA HBO SL SL MN RTA HBO SL SL MN RTA Counting the number of HBO, RTA,SL entries etc. The database consists of 26 worksheets, is it also possible to get the total results from all worksheets to appear on a single summary page? Thanks very much for any help. Jack -- JackJJW ------------------------------------------------------------------------ JackJJW's Profile: http://www.excelforum.com/member.php?action=getinfo&userid...

simple question... (Row cursor?)
.... but one who's answer escapes me. Other than highlighting with formatting each time, how can I keep a row that I am working on delineated from the others after I click into a cell? Even, though I make use of the hiding and freezing possibilities it is still easy for the eye to skip rows. It would be nice if there were some sort of row cursor, that stayed separate from the cell cursor. Cheers, S Try Chip Pearson's ROWLINER add-in to highlight the row and column that is active. http://www.cpearson.com/excel/RowLiner.htm Be aware that it does not work on a protected w...

Help with a simple task please in M04!
I want to set up an auto loan account and then set up a montly electronic Apay to pay from my checking account to the auto loan account and have it show up on both registries (deposit to the auto loan account and withdrawl from the checking account). Using Money 04, I cannot for the life of me get the program to do this. Anyone help please? Note, the auto loan account is to an individual, not a banking institution. I can easily set it up to send a check once a month to the person, but that doesn't allow for tracking the payments in the auto loan account. JTWadsworth jtwadsworth@nospa...

a way to combine archive files
I have been archiving my money files every few years. I was trying to locate a transaction and I had to open each archive file. Is there some way of combining the archive files? You can't - see http://umpmfaq.info/faq.html#Q29. Many of us regard archiving as one of the least helpful things that MS added to Money. -- Regards Bob Peel, Microsoft MVP - Money For UK tips & fixes see http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny. I do not respond to any emails that I have not specifically asked for. "PattiWagen" <sandynpat@hotmail.com> wrote in m...

Counting & Conditions
Hi to evrybody, I have 13 cells in the same row as follows: J14, M14, P14, S14, V14, Y14, AB14, AE14, AH14, AK14, AN14, AQ14, AT14 Each of them contains a number which is a result from a calculation. In Cell BD14, I want a value depending on the number of cells with the following condition (counting): If any 4 or more of the 13 cells has a number < 50 ---> put "Very Slow" in cell DB14 If none of the cells has number < 50 ---> put "Very Fast" in cell BD14 If LESS than 4 cells has number < 50 ---> put "Moderate" in cell BD14 Any suggestions ...

OLAP cubes with Time scale : only 6 months
Hi, I built several OLAP cubes views (Data analysis) with the dimension Time. I built the cube with a date range from 1st january to 31st December 2010. In some views I can display the values for the 12 months of the year. For some views, I can just display the first 6 months (2 quarters) while I selected all the 12 months. What could be the issue? Thanks Xiao: It would really help if you were a bit more specific when you post, such as naming the cubes you're working with and specifying the version of the software you're using, so I' have to guess a little t...

In Excel, look at a date field and return just the month..
I'm looking for an excel formula that looks at a cell containing a date and returns just the month of the date. Hi KC here's some ideas for you try =MONTH(A1) or =A1 and then choose format / cell and in custom type mmm or type mmmm or =text(A1, "mmm") -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "KC" <KC@discussions.microsoft.com> wrote in message news:4A53164A-FB37-460D-A38C-B0F021D890C8@microsoft.com... > I'm looking for an excel formula that looks at a cell containing a date > and &g...

is there a way to select settings on emails
When a contact has multiple emails, is there a way to control how outlook contols which one it selects or is there a way to make it ask you which one you want to send to. Outlook version? -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "bob" <bob@donotspam> wrote in message news:e5TPwAOqEHA.1152@TK2MSFTNGP11.phx.gbl... > When a contact has multiple emails, is there a way to control how outlook > contols which one it selects or is ...