473,320 Members | 2,027 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Order By Month in query not working. Need help with query.

I cannot get my query to order the months in correct order. Here is my SQL.

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW Format$([test data2].[TIME],'mmmm yyyy') AS [TIME By Month], Avg([test data2].[CPU Utilization]) AS [Avg Of CPU Utilization], Avg([test data2].[CPU Utilization - System]) AS [Avg Of CPU Utilization - System], Avg([test data2].[CPU Utilization - User]) AS [Avg Of CPU Utilization - User], Avg([test data2].[CPU Run Queue]) AS [Avg Of CPU Run Queue]
  2. FROM [test data2]
  3. GROUP BY Format$([test data2].[TIME],'mmmm yyyy'), Year([test data2].[TIME])*12+DatePart('m',[test data2].[TIME])-1
  4. Order By 'TIME by Month(date)';
Oct 10 '14 #1
9 1474
Seth Schrock
2,965 Expert 2GB
Please use code tags when posting VBA or SQL code (the [CODE/] button).

You don't have an ORDER BY clause in your query. You only have a GROUP BY which is totally different and offers no sorting capabilities.
Oct 10 '14 #2
Updated Query. I have tried multiple ORDER BY clauses and nothing is working. Is this an MS Access issue?
Oct 10 '14 #3
Rabbit
12,516 Expert Mod 8TB
If 'TIME by Month(date)' is a string field and not a date field or numeric field, then ordering is not going to come out as you expect because strings are ordered by character and character position. Meaning '19' comes before '9' because the first character of each string is 1 and 9 respectively.
Oct 10 '14 #4
Seth Schrock
2,965 Expert 2GB
Also, I think that your syntax would be wrong in your ORDER BY clause. You have the whole clause enclosed with single quotes. I also see that your TIME by Month is also used as the alias for your first field. Is this what you are wanting to sort by? In that case your ORDER BY clause would just need to be
Expand|Select|Wrap|Line Numbers
  1. ORDER BY [Time]
Oct 10 '14 #5
The data type is Date/Time and I have tried using
Expand|Select|Wrap|Line Numbers
  1. ORDER BY [TIME]
. The odd thing is that the query does return the results in the correct order. However, as soon as I do something else in MS Access it changes the order of the months.

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW Format$([test data2].[TIME],'mmmm yyyy') AS Month, Avg([test data2].[CPU Utilization]) AS [Avg Of CPU Utilization], Avg([test data2].[CPU Utilization - System]) AS [Avg Of CPU Utilization - System], Avg([test data2].[CPU Utilization - User]) AS [Avg Of CPU Utilization - User], Avg([test data2].[CPU Run Queue]) AS [Avg Of CPU Run Queue]
  2. FROM [test data2]
  3. GROUP BY Format$([test data2].[TIME],'mmmm yyyy'), Year([test data2].[TIME])*12+DatePart('m',[test data2].[TIME])-1
  4. ORDER BY [Time];
I noticed if I change 'mmmm yyyy' to 'Short Date' it will give the average on a daily basis and apply the dates in the correct order. However, I want the monthly avergae and not the daily average.
Oct 10 '14 #6
Seth Schrock
2,965 Expert 2GB
What do you mean by "do something else" that breaks it?
Oct 10 '14 #7
If I switch views by going to look at my table then go back to view the query results it automatically changes the order.
Oct 10 '14 #8
Seth Schrock
2,965 Expert 2GB
If you open your query in design view, and look at your properties window, does it have anything in the Order By field? This is different than the ORDER BY that is in the SQL part of the query.
Oct 10 '14 #9
NeoPa
32,556 Expert Mod 16PB
Phil:
If I switch views by going to look at my table then go back to view the query results it automatically changes the order.
I'm sorry Phil but that's just not how it works. There must be something you've selected that causes the sort order to change. Simply opening a table won't affect the sort order of a query.

BTW. Your attempt to :
Expand|Select|Wrap|Line Numbers
  1. ORDER BY [TIME]
was close. You need to use :
Expand|Select|Wrap|Line Numbers
  1. ORDER BY Format([TIME],'yyyymm')
This is because that will return a string value that reflects the sort order you require. You will also need to add this into your GROUP BY clause to avoid SQL throwing an error.
Oct 11 '14 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

6
by: Toucan | last post by:
i need to retrieve the most recent timestamped records with unique names (see working query below) what i'm having trouble with is returning the next-most-recent records (records w/ id 1 and 3...
1
by: Colm O'Brien | last post by:
I have a field called year end month where records hold the month financial year end for accounts is stored. i need to query the data base and return all records where year end month is less...
1
by: Johm | last post by:
In my query, consisting of 2 tables, customers and orders,i get the employeeid number ordered with a given customer. The query is the following : SELECT orders.orderid, Customers.Customerid,...
2
by: Frank | last post by:
I'm working on a database to track students in a Title1 program that provides special tutoring in Reading and Math. There is a set of criteria that determines which students get placement in the...
10
by: L. R. Du Broff | last post by:
I own a small business. Need to track a few hundred pieces of rental equipment that can be in any of a few dozen locations. I'm an old-time C language programmer (UNIX environment). If the only...
4
by: technobob | last post by:
I have what seems to be a strange situation. I have a table where Sales are recorded. Most of the time there is only a primary salesman involved (Salesman1). When there is a secondary salesman,...
0
by: djflow | last post by:
Hi! II was wondering if you can help me with SQL query.. Below 7 separated select query works fine(only when they are retrieved separately) But I want to combined them together and so that i...
2
by: sachin shah | last post by:
Hi all i want result as month name from my query i tried with the following query but it give result as month number like (8) select (month(getdate())) as expr i want result as month...
2
by: wevans | last post by:
I have 3 tables, Customer, Offsite Service and service. I need to create a sales report/query based on the results of the user input, which is a date range. I've got it working great with two of the...
2
by: Coxmg | last post by:
This would not let me post new threads for some reason, but heres my problem: I have several related tables. One table lists orders with due dates for SKUs while another table lists components of...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.