• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Order by Error but no Order By Clause (1 Viewer)

Steve@trop

Registered User.
Local time
Today, 10:41
Joined
May 10, 2013
Messages
148
I've been fighting with this problem for a while now. I have a union query that joins the results of about 10 select queries that are all structured the same way but for some reason, two of the select queries are causing an error. I can run all of the queries individually except two of them error out.

The error is:
"A column has been specified more than once in the order by list."

That would be easy to fix but there is NO ORDER BY in the code for these queries! I've narrowed the problem down to the Where clause. When I eliminate part of that clause the error stops. Other than the tables, there is no difference between the clause in the queries that work and the clause in the queries that error out. How do I track this down so I can fix it? Here is the code for one of the queries that isn't working:

Code:
SELECT NorthCompressorRoundT.LoggedAt AS [Time], NorthCompressorRoundT.CompressorID AS ID, NorthCompressorRoundT.CompressorIntegrity AS Integrity, Null AS Integrity2, NorthCompressorRoundT.Status, NorthCompressorRoundT.Notes AS Comments, NorthCompressorT.Type
FROM NorthCompressorT INNER JOIN (LatestCompressorRoundQ INNER JOIN NorthCompressorRoundT ON (LatestCompressorRoundQ.Time = NorthCompressorRoundT.LoggedAt) AND (LatestCompressorRoundQ.ID = NorthCompressorRoundT.CompressorID)) ON NorthCompressorT.AssetNumber = NorthCompressorRoundT.AssetNumber
WHERE (((NorthCompressorRoundT.LoggedAt)=[LatestCompressorRoundQ]![Time]) AND ((NorthCompressorRoundT.CompressorIntegrity)="Known Defect" Or (NorthCompressorRoundT.CompressorIntegrity)="New Defect" Or (NorthCompressorRoundT.CompressorIntegrity)="DFM"));
 

Isaac

Lifelong Learner
Local time
Today, 10:41
Joined
Mar 14, 2017
Messages
2,437
Are any of the entities that you're selecting FROM, linked sql server objects?
 

June7

AWF VIP
Local time
Today, 09:41
Joined
Mar 9, 2014
Messages
3,365
May or may not be contributing to issue but do all the queries use a field named Time? Time is a reserved word and advise not to use reserved words as names.

Remove NorthCompressorRoundT.LoggedAt = [LatestCompressorRoundQ]![Time] from WHERE clause because already have these two fields linked in INNER JOIN clause.
 

Steve@trop

Registered User.
Local time
Today, 10:41
Joined
May 10, 2013
Messages
148
Are any of the entities that you're selecting FROM, linked sql server objects?
Yes, the tables (ending with a "T") are linked sql server objects but the queries (ending with a "Q" are local.
 

Isaac

Lifelong Learner
Local time
Today, 10:41
Joined
Mar 14, 2017
Messages
2,437
Are you able to successfully double-click on the linked objects (ending in T) and they open correctly?
 

Steve@trop

Registered User.
Local time
Today, 10:41
Joined
May 10, 2013
Messages
148
Sure, here you go:

1600282076625.png
 

Isaac

Lifelong Learner
Local time
Today, 10:41
Joined
Mar 14, 2017
Messages
2,437
Ok thanks ... That's where I was going with all of these questions. That error is not from MS Access...it's from SQL Server. Which tells me either there is something wrong with the sql server objects you've been given (most likely Views), or (since you're able to successfully 'run' them by opening them in MS Access) something unique about those objects in combination with your access Union. I will post back if I come up with any ideas on this.. Thanks for all the info.
 

Steve@trop

Registered User.
Local time
Today, 10:41
Joined
May 10, 2013
Messages
148
Thanks for trying Isaac,

One weird thing is I have about 8 or nine other queries that are coded the same with tables linked to the same server database and they don't produce that error. Only difference is they are pointing to different tables. I wonder if it has something to do with the ODBC version is a bit old on these links. Perhaps I'll try updating them and see if that clears it up.
 

Isaac

Lifelong Learner
Local time
Today, 10:41
Joined
Mar 14, 2017
Messages
2,437
It definitely seems like, if it were a problem with the SQL Server View definition, a) I'm not sure how create view even worked, though I don't have time to test now, and b) If that were the case you shouldn't have been able to run the view by opening the Access linked table...

Weird.

You could also post the entire SQL of your final query in case that sheds any light to anyone?
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:41
Joined
Feb 19, 2002
Messages
29,759
If you open a form or query and manually sort it, Access saves that action when you close the object. If a change is made to the table schema, you may later get errors.

Open the query in design view and look at the properties to see if there is a saved sort on a column that is no longer in the query.
 

Steve@trop

Registered User.
Local time
Today, 10:41
Joined
May 10, 2013
Messages
148
Thanks Pat,

I actually copied the SQL code from the query into an entirely new query for troubleshooting and I get the same error. That probably rules out any saved actions on the object.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 13:41
Joined
Feb 19, 2002
Messages
29,759
If the tables are SQL Server (or other RDBMS or spreadsheet), try relinking the table. Access automatically updates source table changes when the source table is Jet/ACE but it does not update the definitions automatically for other linked tables.
 

Users who are viewing this thread

Top Bottom