Group By in Access

Grop by month for access database

Page 1 of 1

12 Replies - 13245 Views - Last Post: 15 December 2009 - 10:44 PM

#1 [email protected]  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 13
  • Joined: 26-July 09

Group By in Access

Posted 25 November 2009 - 03:25 AM

Hi i need month and year of a table and group the table with respect to month and year. The tableX has a column called servicetime of type datetime. The database i am using is MS Access 2003. I need to show report on how many hours the service was done in Dec 2009,Nov 2009.

Any help would be appriciated.

SELECT [Month],[Year] from tableX Group by [Month],[Year]


Thanks,

This post has been edited by [email protected]: 25 November 2009 - 03:32 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Group By in Access

#2 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Group By in Access

Posted 27 November 2009 - 02:13 PM

You have two fields Month and Year? Give sample data.
Was This Post Helpful? 0
  • +
  • -

#3 [email protected]  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 13
  • Joined: 26-July 09

Re: Group By in Access

Posted 08 December 2009 - 12:35 AM

Sorry my explanation seems not quite descriptive.
Let me explain again.

I have a table named Service which has columns: ServiceTime,ServiceDuration,Month,Year

ServiceTime(datetime) ServiceDuration(int) Month(text) Year[text]
12/11/2009 11:00:00 AM 180 November 2009
13/11/2009 11:00:00 AM 180 November 2009
14/11/2009 11:00:00 AM 180 November 2009
25/11/2009 11:00:00 AM 180 November 2009
02/12/2009 11:00:00 AM 180 December 2009
16/12/2009 11:00:00 AM 180 December 2009

And my query is
SELECT ([Month]+' '+[Year]) AS ServiceDate,Sum(serviceduration)/60 As serviceduration FROM Services where ServiceTime>=#12/11/2009# AND ServiceTime <= #20/12/2009#

Now i need to group this query by ServiceTime.Month and ServiceTime.Year not by Month(text) and Year(text) and also i have to order it by ServiceTime.Month in ascending order.

Thanks
Was This Post Helpful? 0
  • +
  • -

#4 [email protected]  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 13
  • Joined: 26-July 09

Re: Group By in Access

Posted 10 December 2009 - 04:21 AM

Hi,
I figured out using the following code. But i did not user Month(text) and Year(text) column. Instead ServiceTime Column to find the month and year and group by month and year.

But the problem was it did not sort according to month and year.

SELECT Format(ServiceTime, 'mmmm') + ' ' + Format(ServiceTime, 'yyyy') AS ServiceDate, SUM(ServiceDuration) / 60 AS serviceduration
FROM Services
WHERE (ServiceTime >= #09/05/2009#) AND (ServiceTime <= #12/10/2010#)
GROUP BY Format(ServiceTime, 'mmmm'), Format(ServiceTime, 'yyyy')
ORDER BY Format(ServiceTime, 'mmmm'), Format(ServiceTime, 'yyyy')

Result:
ServiceDate serviceduration
December 2009 30
January 2010 24
November 2009 6

Actually, display order should be November 2009, December 2009 and Finally January 2010.

If any could sort , please post code.

Thanks
Was This Post Helpful? 0
  • +
  • -

#5 ForcedSterilizationsForAll  Icon User is offline

  • D.I.C Addict

Reputation: 33
  • View blog
  • Posts: 506
  • Joined: 16-July 09

Re: Group By in Access

Posted 10 December 2009 - 01:05 PM

I love consultants.

You need to order by the year first, and you need to convert the month to its associated number.

And I won't "POST TEH CODEZ" but will tell you that you're making things a little more difficult than they need to be. Why are you creating a serviceDate column when the ServiceTime one already gives you the month and year?
Was This Post Helpful? 0
  • +
  • -

#6 [email protected]  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 13
  • Joined: 26-July 09

Re: Group By in Access

Posted 10 December 2009 - 11:26 PM

Hi,

I tried this,but it gives error that says "Undefined Year(),Month() function.
SELECT YEAR(ServiceTime) AS Expr3,MONTH(ServiceTime) AS Expr1, SUM(ServiceDuration) AS Expr2
FROM Services
GROUP BY MONTH(ServiceTime), YEAR(ServiceTime)

can you give an easy way to solve this. Also how do i convert the month to its associated number. if you could provide me the query, that will be great.


Thanks.

This post has been edited by [email protected]: 11 December 2009 - 12:47 AM

Was This Post Helpful? 0
  • +
  • -

#7 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Group By in Access

Posted 13 December 2009 - 01:40 PM

Use DatePart function to extract any part of a date.
Was This Post Helpful? 0
  • +
  • -

#8 [email protected]  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 13
  • Joined: 26-July 09

Re: Group By in Access

Posted 13 December 2009 - 11:46 PM

Hi June7
Thanks for the reply.

I tried the datepart as you said like
SELECT Datepart('yyyy', ServiceTime) AS [Year], Datepart('m', ServiceTime) AS [Month], SUM(ServiceDuration) AS ServiceDuration
FROM Services
WHERE (ServiceTime >= #09/05/2008#) AND (ServiceTime <= #12/10/2010#)
GROUP BY Datepart('yyyy', ServiceTime), Datepart('m', ServiceTime)

i get this result. This is the way i need, but have to concat month and year.
Month Year ServiceDuration
4 2009 480
5 2009 480
6 2009 480
7 2009 600
8 2009 480

Actually i need to concat Month and Year as ServiceDate and display like below

ServiceDate ServiceDuration
April 2009 480
May 2009 480
June 2009 480
July 2009 600
August 2009 480


How can i do this.

Thanks,
Was This Post Helpful? 0
  • +
  • -

#9 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Group By in Access

Posted 14 December 2009 - 01:00 PM

Use MonthName function:
SELECT Datepart('yyyy', ServiceTime) AS [Year], MonthName(Datepart('m', ServiceTime)) AS [Month], SUM(ServiceDuration) AS ServiceDuration
FROM Services
WHERE (ServiceTime >= #09/05/2008#) AND (ServiceTime <= #12/10/2010#)
GROUP BY Datepart('yyyy', ServiceTime), Datepart('m', ServiceTime)

This post has been edited by June7: 14 December 2009 - 01:02 PM

Was This Post Helpful? 0
  • +
  • -

#10 [email protected]  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 13
  • Joined: 26-July 09

Re: Group By in Access

Posted 15 December 2009 - 03:20 AM

Hi June7,

I did sth like this

I could get the result i wanted when i run the query in MS Access Query tab.

 
SELECT a.y & " "& Monthname(a.m ) AS ServiceDate, a.sumofserviceduration FROM (SELECT		   DatePart('yyyy',ServiceTime) AS [Y],  DatePart('m',ServiceTime) AS [M] , Sum(services.serviceduration) AS SumOfserviceduration FROM services WHERE (((services.Servicetime)>=#9/5/2008# And (services.Servicetime)<=#12/10/2010#)) GROUP BY DatePart('yyyy',ServiceTime), DatePart('m',ServiceTime)) AS a;




And the result i get is , which is the result i wanted

Group by Month year 2
ServiceDate sumofserviceduration
2009 April 480
2009 May 480
2009 June 480
2009 July 600
2009 August 480
2009 September 480
2009 October 600
2009 November 1440
2009 December 2040
2010 January 1920
2010 February 960

But when i run the query in code behind in C#.Net

 
string sql=SELECT a.y & " "& Monthname(a.m ) AS ServiceDate, a.sumofserviceduration
FROM (SELECT DatePart('yyyy',ServiceTime) AS [Y],  DatePart('m',ServiceTime) AS [M] , Sum(services.serviceduration) AS SumOfserviceduration
FROM services
WHERE (((services.Servicetime)>=#9/5/2008# And (services.Servicetime)<=#12/10/2010#))
GROUP BY DatePart('yyyy',ServiceTime), DatePart('m',ServiceTime)) AS a;

OleDbCommand dbComm = new OleDbCommand(sql, con);
OleDbDataReader myReader = dbComm.ExecuteReader(Commandbehavior.CloseConnection);



I get this error " Undefined function 'Monthname' in expression".

If i remove MonthName funtion , however this will execute in the code behind too and the result is

Group by Month year 2
ServiceDate sumofserviceduration
2009 4 480
2009 5 480
2009 6 480
2009 7 600
2009 8 480
2009 9 480
2009 10 600
2009 11 1440
2009 12 2040
2010 1 1920
2010 2 960


But i want the result like above.

What should i do to get the result i wanted.

Thanks

This post has been edited by [email protected]: 15 December 2009 - 03:26 AM

Was This Post Helpful? 0
  • +
  • -

#11 ForcedSterilizationsForAll  Icon User is offline

  • D.I.C Addict

Reputation: 33
  • View blog
  • Posts: 506
  • Joined: 16-July 09

Re: Group By in Access

Posted 15 December 2009 - 12:05 PM

Try making a view with that query and then call the view in C#.
Was This Post Helpful? 0
  • +
  • -

#12 June7  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Group By in Access

Posted 15 December 2009 - 12:59 PM

So Monthname is a VB function. Don't know what would be equivalent in C#. Post question in C# forum.
Was This Post Helpful? 0
  • +
  • -

#13 [email protected]  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 13
  • Joined: 26-July 09

Re: Group By in Access

Posted 15 December 2009 - 10:44 PM

I tried to use Case Statement...but i get error

Sql Exception Error
Error Source:System.Data
Error Message: IErrorInfo.GetDescription failed with E_FAIL(0X80004005).

So instead of Case , I used iif() statement and worked .

Here is the query for it...

SELECT iif(M=1,'January',iif(M=2,'Februrary',iif(M=3,'March',iif(M=4,'April',iif(M=5,'May',iif(M=6,'June',iif(M=7,'July',iif(M=8,'August', iif(M=9,'September',iif(M=10,'October',iif(M=11,'November','December')))))))))))
&' ' &Y

AS ServiceDate, SumOfserviceduration
FROM (SELECT DatePart('yyyy', ServiceTime) AS Y, DatePart('m', ServiceTime) AS M, SUM(ServiceDuration) AS SumOfserviceduration
FROM Services
WHERE (ServiceTime >= #1/09/2009#) AND (ServiceTime <= #12/31/2010#)
GROUP BY DatePart('yyyy', ServiceTime), DatePart('m', ServiceTime)) a


Thank you all of you. Really appreciated.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1