Microsoft Access can't represent the join expression "<=" operators

Question

I have the following SQL

SELECT 
a.StudentsID, 
a.ClassGroup, 
a.FinalGrade, 
COUNT(*) AS ranknumber

FROM FinalAVG AS a 
INNER JOIN FinalAVG AS b 
ON (a.ClassGroup = b.ClassGroup) 
AND (a.FinalGrade <= b.FinalGrade)

GROUP BY 
a.ClassGroup, 
a.StudentsID, 
a.FinalGrade

HAVING COUNT(*) <= 3
ORDER BY a.ClassGroup, COUNT(*) DESC; 

It works well. However when I switch to design view, it shows me the following error

Microsoft Access can't represent the join expression a.FinalGrade <= 
b.FinalGrade

Any help please ?

Answer 1

That's by design and a limitation of the GUI designer.

However, the SQL view is available for this type of queries.

Yes, just go ahead and correct errors if any. As is, your SQL seems valid.

Answer 2

Ms Access Design view can only handle queries where both path part of the join are of the same datatype and there is equality between them e.g

ON  A.ID = B.ID

But on the SQL view you can do a;l kind of associations..a very common case is when one part is of String DataType (but numeric) and the other is Integer so you can join them like this :

A.ID = Cint(B.ID)

Answer 3

I recently discovered Microsoft Access currently allows the ChrW(-257) character, which is equivalent to ChrW(65279), to be in field names. This is a zero-width no-break space character, so it is invisible. You have to use the AscW() function to detect this character in a string. Having this character in the field name in one table and not in another table can cause the false error message, "Access can't represent the join expression [table1].[field] = [table2].[field].", when you attempt to view a query in Design View. If you print such a field name in the Immediate window of the Visual Basic window, with an expression like:

? CurrentDb.TableDefs("tablename").Fields("fieldname").Name

then the ChrW(65279) character appears as a question mark at the start of the field name.

The ChrW(65279) character can also appear in the SQL text of queries.

In hexadecimal, the -257 or 65279 character code is FEFF, which is used to signify the endianness of text. Someone might have copied and pasted a field name that contains this character from another program into Microsoft Access.