Hi,

   Okay, this sounds simple, but I can't get my head around it this morning.

   Here is the pertinent table data:

tblComputer
PKID
ComputerName
Date

   I keep track of computers as they log in to the network. They log in once a month. So for each computer that logs in and scans, there is one entry in this table per month.

   Ex:

PKID ComputerName Date
1 wmmcodnal 8/01/2004
2 wanother 8/01/2004
3 wmmcdonal 9/01/2004
4 wmmcdonal 10/01/2004
5 wanother 10/01/2004

   I need to count the number of computers that are in the table per month. I would like a report to look like this:

August: 2
September: 1
October: 2

   I have gotten this far:

'=================================
SELECT DatePart("m",[Date]) AS MonthNo
FROM dbo_tblComputer;
'=================================

   Not much, eh?

Thanks for your help in advance.


mmcdonal

__________________
mmcdonal

Look it up at: http://wrox.books24x7.com

Try this...

SELECT DatePart("m",[Date]) AS MonthNo, Count([MonthNo]) AS [Count]
FROM tblComputer
GROUP BY DatePart("m",[Date]);

And then I am thinking in your report your could just bind MonthNo to a combobox with a list of Month Names and Numbers.

Mike
EchoVue.com

You are a genius!

Thanks, that worked great.

In the Report, I used this code in an unbound text box to reveal the Month Name:

'=======================
=MonthName([MonthNo])
'=======================

Then I made MonthNo invisible.

Thanks again for the help!




mmcdonal

No Problem!! Thank-you as well. I have been playing with the DatePart Function since I saw your post and it is going to save me a lot of work in the coming year, so this is definitely a win/win!

Mike
EchoVue.com

This is how I refined the query:

'=========================================
SELECT DatePart("m",[Date]) AS MonthNo, DatePart("yyyy",[Date]) AS YearNo, Count([MonthNo]) AS [Count]
FROM dbo_tblComputer
GROUP BY DatePart("m",[Date]), DatePart("yyyy",[Date])
ORDER BY DatePart("m",[Date]), DatePart("yyyy",[Date]);
'=========================================


mmcdonal