将SQL Server查询转换为访问权限(Convert SQL Server query to access)

将SQL Server查询转换为访问权限(Convert SQL Server query to access)

我有以下SQL Server查询，不能为了上帝的爱，让它在MS Access中工作。

select U.voorstellingnummer, U.uitvoeringnummer, U.zaalnaam, S.rijnummer, S.stoelnummer from Uitvoering as U inner join Stoel as S on U.zaalnaam = S.zaalnaam where U.voorstellingnummer = 4 AND U.uitvoeringnummer = 1 AND -- (S.rijnummer, S.stoelnummer) not in (select(B.rijnummer, B.stoelnummer) ('rij '+ cast(S.rijnummer as varchar(3)) + ' stoel ' + cast(S.stoelnummer as varchar(3))) not in (select('rij '+ cast(B.rijnummer as varchar(3)) + ' stoel ' + cast(B.stoelnummer as varchar(3))) from Bezetting as B where B.voorstellingnummer = 4 AND B.uitvoeringnummer = 1)

我已经添加了几天了，但它在第一个AS上一直给我语法错误。

该查询在SQL Server中运行良好。 我究竟做错了什么？

谢谢你的时间。

I've got the following SQL Server query and can not for the love of God get this to work in MS Access.

select U.voorstellingnummer, U.uitvoeringnummer, U.zaalnaam, S.rijnummer, S.stoelnummer from Uitvoering as U inner join Stoel as S on U.zaalnaam = S.zaalnaam where U.voorstellingnummer = 4 AND U.uitvoeringnummer = 1 AND -- (S.rijnummer, S.stoelnummer) not in (select(B.rijnummer, B.stoelnummer) ('rij '+ cast(S.rijnummer as varchar(3)) + ' stoel ' + cast(S.stoelnummer as varchar(3))) not in (select('rij '+ cast(B.rijnummer as varchar(3)) + ' stoel ' + cast(B.stoelnummer as varchar(3))) from Bezetting as B where B.voorstellingnummer = 4 AND B.uitvoeringnummer = 1)

I've been add it for a couple of days now but it keeps giving me syntax errors on the first AS.

The query works perfect in SQL Server. What am I doing wrong?

Thanks for your time.

最满意答案

我通常会像瘟疫一样避免使用Access，如果我可以的话。

但是，看起来您的问题可能在演员阵容中。 如果我没记错，Access将执行从numeric到char的隐式转换。 如果是这种情况，此查询应该有效：

select U.voorstellingnummer, U.uitvoeringnummer, U.zaalnaam, S.rijnummer, S.stoelnummer from Uitvoering as U inner join Stoel as S on U.zaalnaam = S.zaalnaam where U.voorstellingnummer = 4 AND U.uitvoeringnummer = 1 AND ('rij ' & S.rijnummer & ' stoel ' & S.stoelnummer) not in ( select ('rij ' & B.rijnummer & ' stoel ' & B.stoelnummer) from Bezetting as B where B.voorstellingnummer = 4 AND B.uitvoeringnummer = 1 )

如果隐式转换不起作用，Cstr（）应该将数字转换为字符串。 如果你只想要左边的3个字符，你可能需要做左边的事情（cstr（S.rijnummer），3）。

编辑：另外，我不完全确定你可以在are子句中使用子查询。 如果没有，你可能需要将其中的大多数作为子查询然后离开加入Bezetting并使用isnull（Bezetting.rijnummer） - 或者在Access中的任何内容:-)

I typically avoid Access like the plague if I can lol.

However, it looks like your problem might be in the casts. If I remember right, Access will do an implicit conversion from numeric to char. If that's the case, this query should work:

select U.voorstellingnummer, U.uitvoeringnummer, U.zaalnaam, S.rijnummer, S.stoelnummer from Uitvoering as U inner join Stoel as S on U.zaalnaam = S.zaalnaam where U.voorstellingnummer = 4 AND U.uitvoeringnummer = 1 AND ('rij ' & S.rijnummer & ' stoel ' & S.stoelnummer) not in ( select ('rij ' & B.rijnummer & ' stoel ' & B.stoelnummer) from Bezetting as B where B.voorstellingnummer = 4 AND B.uitvoeringnummer = 1 )

If the implicit conversions don't work, Cstr() should convert the numbers to strings. If you want only the left 3 characters, you'd probably need to do something like left(cstr(S.rijnummer),3).

Edit: Also, I'm not entirely sure you can use a subquery in the were clause. If not, you might need to make the majority of this a sub query then left join Bezetting to it and use where isnull(Bezetting.rijnummer) - or whatever it is in Access :-)