-
May 7th, 2022, 10:49 PM
#1
Thread Starter
Addicted Member
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 addedI would really like to show the month along with the totals for that month. Is it possible on one Query?
-
May 7th, 2022, 11:11 PM
#2
Re: get dates grouped by month in access?
Try grouping by the expression rather than the alias.
-
May 7th, 2022, 11:15 PM
#3
Thread Starter
Addicted Member
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
-
May 7th, 2022, 11:39 PM
#4
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.
-
May 7th, 2022, 11:53 PM
#5
Thread Starter
Addicted Member
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!
-
May 7th, 2022, 11:58 PM
#6
Re: get dates grouped by month in access?
Originally Posted by pmeloy
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.
-
May 8th, 2022, 12:08 AM
#7
Thread Starter
Addicted Member
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
-
May 8th, 2022, 12:17 PM
#8
Re: get dates grouped by month in access?
Originally Posted by pmeloy
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
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|