Join Expression Not Supported

[ubsman]

I have a very simple SQL statement but Access cannot process it. Any ideas on how I can do it differently?



This is the SQL (manually typed since copy and paste doesn't work in this forum):


SELECT left_value,count(right_value) FROM
LeftTable LEFT JOIN RightTable
on left_value = right_value
GROUP BY left_value

Database definition:

LeftTable contains 1 column called left_value
RightTable contains 1 column called right_value

Data in LeftTable:
0
1
-1
.6667

Data in RightTable:
0
1

Desired Result (it won't display here quite right, but you get the idea)

left_value   count
=======   =====
0                  1
1                  1
-1                 0
.6667            0

[marianne]

try this:

SELECT LeftTable.left_value, Count(RightTable.right_value) AS CountOfright_value
FROM LeftTable LEFT JOIN RightTable ON LeftTable.left_value = RightTable.right_value
GROUP BY LeftTable.left_value;

study the difference. good luck.

[ubsman]

try this:

SELECT LeftTable.left_value, Count(RightTable.right_value) AS CountOfright_value
FROM LeftTable LEFT JOIN RightTable ON LeftTable.left_value = RightTable.right_value
GROUP BY LeftTable.left_value;

study the difference. good luck.

That runs better than before, but still it doesn't count the 0 values. It just shows a count of 0 for 0 but should show a count of 1.

[tinytree]

try this:
SELECT a.left_value, Count(a.right_value) AS CountOfright_value
FROM (select left_value from LeftTable LEFT JOIN RightTable ON LeftTable.left_value = RightTable.right_value) as a
GROUP BY a.left_value;