Results 1 to 8 of 8

Thread: get dates grouped by month in access?

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Dec 2012
    Posts
    136

    get dates grouped by month in access?

    I'm doing great selecting dates now but I have a feature that shows the more than one day at a time (month,quarter or year) I would like to show individual month totals but can't figure out how.

    My current query is
    Code:
        query = "SELECT Count(*) as [#Sales],SUM(totalincome) as [Income], SUM(totalcost) as [Cost],  ([Income]-[Cost]) as [Profit],Round(((1-([Cost]/[Income]))*100),1) as [Percent] FROM orders WHERE thedate >= @startDate And thedate < @endDate"
    I tried adding
    Code:
    Format(thedate,'mmm') as [Date]
    but Access doesn't like that in the aggregate query even though I also added
    Code:
     GROUP BY[Date]
    I would really like to show the month along with the totals for that month. Is it possible on one Query?

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: get dates grouped by month in access?

    Try grouping by the expression rather than the alias.

  3. #3

    Thread Starter
    Addicted Member
    Join Date
    Dec 2012
    Posts
    136

    Re: get dates grouped by month in access?

    Yes, that worked. I tried grouping by thedate before but it gives one record for each order. I was hoping to get them one row per month instead

  4. #4
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: get dates grouped by month in access?

    I said group by the expression rather than the alias. The alias is [Date]. The expression is Format(thedate,'mmm'). If you do what I said then you'll get the result you want.

    Mind you, keep in mind that you're grouping just by month there. If your date range is large enough then you may be grouping data from different years.

  5. #5

    Thread Starter
    Addicted Member
    Join Date
    Dec 2012
    Posts
    136

    Re: get dates grouped by month in access?

    Well I'll be a *bleep* I thought for sure I could only use literals in group or sort. This is a revelation for me!

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: get dates grouped by month in access?

    Quote Originally Posted by pmeloy View Post
    Well I'll be a *bleep* I thought for sure I could only use literals in group or sort. This is a revelation for me!
    It always feels like you should be able to use aliases (not literals) there but for some reason you can't. I'm not sure what the reasoning is for that. It feels dirty to have to repeat the same expression that you've already aliased.

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Dec 2012
    Posts
    136

    Re: get dates grouped by month in access?

    Actually it looks like using a variable but unless it is exactly the same expression used in the select it just crashes so I think it's really using a literal just that the literal isn't literally a column name

  8. #8
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,046

    Re: get dates grouped by month in access?

    Quote Originally Posted by pmeloy View Post
    I'm doing great selecting dates now but I have a feature that shows the more than one day at a time (month,quarter or year) I would like to show individual month totals but can't figure out how.
    you do have the PIVOT option in Access, here a sample

    the SQL
    Code:
    TRANSFORM Sum(CCur([Order Details].[Unitprice]*[Quantity]*(1-[Discount])/100)*100) AS Total
    SELECT Format([OrderDate],"yyyy") AS [in Year], Customers.CompanyName
    FROM (Customers INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID
    GROUP BY Format([OrderDate],"yyyy"), Customers.CompanyName
    PIVOT Format([OrderDate],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
    and a Image to see the result of the SQL
    Name:  Pivot Year.jpg
Views: 134
Size:  23.8 KB
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

Posting Permissions

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



Click Here to Expand Forum to Full Width