I cannot get my query to order the months in correct order. Here is my SQL. - 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]
-
FROM [test data2]
-
GROUP BY Format$([test data2].[TIME],'mmmm yyyy'), Year([test data2].[TIME])*12+DatePart('m',[test data2].[TIME])-1
-
Order By 'TIME by Month(date)';
9 1474
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.
Updated Query. I have tried multiple ORDER BY clauses and nothing is working. Is this an MS Access issue?
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.
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
The data type is Date/Time and I have tried using
. 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. - 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]
-
FROM [test data2]
-
GROUP BY Format$([test data2].[TIME],'mmmm yyyy'), Year([test data2].[TIME])*12+DatePart('m',[test data2].[TIME])-1
-
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.
What do you mean by "do something else" that breaks it?
If I switch views by going to look at my table then go back to view the query results it automatically changes the order.
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.
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 :
was close. You need to use : - 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.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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,...
|
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...
|
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...
|
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,...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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
|
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...
| |