Hello and happy new year everyone.

[Webkins]

Hello and happy new year everyone. I am having troulbe with an Access 2010 query.

There are 2 MSSql 2000 tables involved:
1. mwo.dbo.mwo
2. mwo.dbo.numbers

Here is the query which works just fine in SQL 2000 Query Analyzer but in an Access 2010 query I get the following error:

"JOIN expression not supported"

SELECT 2011 AS completed_year, Numbers.Num As completed_week, coalesce(count(completed_week), 0) AS completed_wo
FROM [ODBC;DRIVER=SQL Server;SERVER=MSServer001;DATABASE=mwo;Trusted_Connection=Yes].Numbers
left Join mwo ON numbers.Num = completed_week and Numbers.Num Between 1 and 52 And (responsible_status = 'complete') and (completed_year = 2011)
GROUP BY completed_week, numbers.Num
ORDER BY completed_week;

Any ideas, anything obvious. Thanks so very much

 

[dhookom]

What does work from Access?
Does the query work if you take out joins?

Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

BTW: "Hello and happy new year everyone" isn't an appropriate subject line for a posting.

Duane
Hook'D on Access
MS Access MVP

 

[Webkins]

Yes, I know. I am not sure how that got there in the title and was disappointed to see that after I had submitted the post. I didn't preview the post, not my intention, sorry :-(

There are 2 MSSql 2000 tables involved:
1. mwo.dbo.mwo
2. mwo.dbo.numbers

Here is the query which works perfect in SQL 2000 Query Analyzer:
SELECT 2011 AS completed_year, num AS completed_week, coalesce(count(completed_week), 0) AS completed_wo
FROM numbers
LEFT JOIN mwo ON num = completed_week AND (responsible_status = 'complete') AND (completed_year = 2011)
GROUP BY completed_week, num;

The query in Access 2010. I get the following error:
"JOIN expression not supported"

SELECT 2011 AS completed_year, num AS completed_week, coalesce(count(completed_week), 0) AS completed_wo
FROM [ODBC;DRIVER=SQL Server;SERVER=MSServer001;DATABASE=mwo;Trusted_Connection=Yes].numbers
LEFT JOIN mwo ON num = completed_week AND (responsible_status = 'complete') AND (completed_year = 2011)
GROUP BY completed_week, num

Any ideas, anything obvious. Thanks so very much

 

[dhookom]

I asked/suggested you remove the join and try a simple SQL statement to see if you can get it to work.

Duane
Hook'D on Access
MS Access MVP