Sort by clause conflicts with excellent access?

Question

Please help me with this, as I have not been able to overcome this problem.

When trying to fulfill this statement:

SELECT distinct grade FROM tblStudents ORDER BY Val([grade]),grade; 

access tells me that ORDER BY clause Val([grade]) conflicts with Distinct

How can i fix this?

Thank you in advance

Answer 1

Using the DISTINCT keyword has the same effect as grouping by all columns in a SELECT :

 SELECT grade FROM tblStudents GROUP BY grade ORDER BY VAL(grade), grade; 

Note. I had to delete the lines where grade IS NULL , otherwise I received the error message "Data type mismatch in the criteria expression".

Answer 2

You cannot order a column that is not specified in a separate expression; if you want the class to be bound to an integer;

 SELECT DISTINCT Val([grade]) FROM tblStudents ORDER BY Val([grade]); 

Answer 3

Using this:

 SELECT DISTINCT Val([grade]) FROM tblStudents ORDER BY Val([grade]); 

Non-numeric entries are displayed as 0.

But below, both numerical and non-numerical entries are shown:

 SELECT grade FROM tblStudents GROUP BY grade ORDER BY VAL(grade), grade;