We help IT Professionals succeed at work.

Query Error from Access to SQL

530 Views
Last Modified: 2008-02-01
I have the following query as the row source for a combo box.

SELECT DISTINCT Qry_OrdersOverdueList.CustomerTitle, Qry_OrdersOverdueList.AccountingRef
FROM Qry_OrdersOverdueList
ORDER BY Qry_OrdersOverdueList.CustomerTitle;

It works fine with Access linked tables but as soon as I moved the tables to SQL server and link via ODBC I get the following error.

ODBC-- call failed
[Microsoft][ODBC SQL Server Driver][SQL Server] ORDER BY items must appear in the select list if SELECT DISTINCT is specified. (#145)

Qry_OrdersOverdueList.CustomerTitle appears in the select list so I can't figure it out.

Any help greatly appreciated.

Leigh
Comment
Watch Question

Commented:
I don't think you need all that in the select statement try this;

SELECT DISTINCT CustomerTitle, AccountingRef
FROM Qry_OrdersOverdueList
ORDER BY CustomerTitle ASC

If you do not want ASC, try DESC

Author

Commented:
Thanks mdefalco but it made no difference.

Commented:
can you run that query in query analyzer and see if it returns the results you want?

Author

Commented:
I have managed to get around the problem by removing the ORDER BY clause altogether. As it happens in this situation the sort field is the first field so the results are in the order I need anyway.  That is not to say that there isn't a problem however.

I have had "filtering" problems with nested queries previously which turned out to be a microsoft bug for which I was able to get a hotfix and I am thinking that this may be a similar problem.

I will leave this post open just in case someone has other ideas or more information because I would still like a proper solution if one is available.
Giuseppe PizzutoIT Director
CERTIFIED EXPERT

Commented:
have you tried the clause ORDER BY 1 ?
regards
Jim HornSQL Server Data Dude
CERTIFIED EXPERT
Most Valuable Expert 2013
Author of the Year 2015

Commented:
(1)  Lose the semi-colon (;) at the end of your SQL statement, as SQL Server does not recognize it.
(2)  {wild guess}  Is Qry_OrdersOverdueList a local query in Access?  If so, SQL Server would not recognize it, as it's not actually in SQL Server.

Author

Commented:
<have you tried the clause ORDER BY 1 ?>  Yes I have, No difference.


<(1)  Lose the semi-colon (;) at the end of your SQL statement, as SQL Server does not recognize it. >  No difference.

<(2)  {wild guess}  Is Qry_OrdersOverdueList a local query in Access?  If so, SQL Server would not recognize it, as it's not actually in SQL Server. >
Yes, Qry_OrdersOverdueList is a local query in Access, but so is the problem query.  Also I wouldn't have thought it would be recognition problem because the query works without problem until you try to sort.

ie This works,
SELECT DISTINCT Qry_OrdersOverdueList.CustomerTitle, Qry_OrdersOverdueList.AccountingRef
FROM Qry_OrdersOverdueList;

This doesn't
SELECT DISTINCT Qry_OrdersOverdueList.CustomerTitle, Qry_OrdersOverdueList.AccountingRef
FROM Qry_OrdersOverdueList
ORDER BY Qry_OrdersOverdueList.CustomerTitle;

Also curiously this works:

SELECT Qry_OrdersOverdueList.CustomerTitle, Qry_OrdersOverdueList.AccountingRef
FROM Qry_OrdersOverdueList
ORDER BY Qry_OrdersOverdueList.CustomerTitle;

Obviously however by removing the DISTINCT I end up with duplicate values which is not what I need.
It seems to have a problem with DISTINCT and ORDER BY together but not individually.

Commented:
Just a thought.
Has the table changed in SQL at all since you linked it into access?  i.e. Moved the order of the fields.
IT Director
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
gpizzuto.

Your sql returns error that Accounting ref is not part of an aggregate function however, slightly modified version seems to work.

SELECT Qry_OrdersOverdueList.CustomerTitle, Qry_OrdersOverdueList.AccountingRef
FROM Qry_OrdersOverdueList
GROUP BY Qry_OrdersOverdueList.CustomerTitle, Qry_OrdersOverdueList.AccountingRef
ORDER BY Qry_OrdersOverdueList.CustomerTitle;

While it works I think that this is still a "Work Around" as opposed to a solution, but I am also convinced that it is a microsoft bug so anything that can keep me going with my project is a big help.

Thanks for all your replies.

Leigh
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.