Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Hello and happy new year everyone.

Status
Not open for further replies.

Webkins

Programmer
Dec 11, 2008
118
US
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top