Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi,

    It's been several years since I used Access, so have alot to refresh on and learn.

    I have the following SQL code

    SELECT ED_data.Region, ED_data.Departure_Date, Count(ED_data.URN) AS CountOfURN
    FROM ED_data
    GROUP BY ED_data.Region, ED_data.Departure_Date;

    I would like to group the Departure_Date field into months. The data is currently provided by day. I formatted the field to mmm/yyyy but this only resulted in Region then 30 entires for each month. I just want 12 entries for each region (one for each month).

    Can someone tell me how to group the data in the query so that I only have one amount for each month?

    Thanks
    capri

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Quote Originally Posted by capri View Post
    Hi,

    It's been several years since I used Access, so have alot to refresh on and learn.

    I have the following SQL code

    SELECT ED_data.Region, ED_data.Departure_Date, Count(ED_data.URN) AS CountOfURN
    FROM ED_data
    GROUP BY ED_data.Region, ED_data.Departure_Date;

    I would like to group the Departure_Date field into months. The data is currently provided by day. I formatted the field to mmm/yyyy but this only resulted in Region then 30 entires for each month. I just want 12 entries for each region (one for each month).

    Can someone tell me how to group the data in the query so that I only have one amount for each month?

    Thanks
    capri
    Do you want months accumulated over years?
    If so just use mmmm as the format.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    The Month function extracts the month from a date. If the data runs over multiple years you would also need to group by Year as well.

    Code:
    SELECT ED_data.Region, Year(ED_data.Departure_Date) As DeptYear , Month(ED_data.Departure_Date) As DeptMonth, Count(ED_data.URN) AS CountOfURN
    FROM ED_data
    GROUP BY ED_data.Region, Year(ED_data.Departure_Date), Month(ED_data.Departure_Date),
    Order by ED_data.Region, Year(ED_data.Departure_Date), Month(ED_data.Departure_Date)
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •