求：将一条SQL查询语句转成Access(100分)

select danwei.bmid,danwei.Tnumber,danwei.name,danwei.gwzg,danwei.gwfg,danwei.gwzj,danwei.gwcj,isnull(A.ZG,0) as ZG,isnull(B.FG,0) as FG,isnull(C.ZJ,0) as ZJ,isnull(D.CJ,0) as CJ from danwei
left join (select dwid,COUNT(*) as ZG from ryb where (zcjb=1)group by dwid) as A on A.dwid=danwei.Tnumber
left join (select dwid,COUNT(*) as FG from ryb where (zcjb=2)group by dwid) as B on B.dwid=danwei.Tnumber
left join (select dwid,COUNT(*) as ZJ from ryb where (zcjb=3)group by dwid) as C on C.dwid=danwei.Tnumber
left join (select dwid,COUNT(*) as CJ from ryb where (zcjb=4) or (zcjb=5)group by dwid) as D on D.dwid=danwei.Tnumber

把ISNULL 换成IIF 就可以了吧，例如

isnull(A.ZG,0)改为
iif(a.zg is null,0,a.zg)
其他地方应该不用改。

嗯，这是一处。
可还有一处：
Access不支持多表联接查询，也就是不支持2个以上的Left Join...On

1、isnull转换为iif
2、如果是多个表关联，要加上括号((a left join b on ) left join c on )

用inner join替換left join試試看

好样的，，这样恐怕不行

实在不行我让客户用SQL Server得了...

除了1楼说的isnull区别之外，access要实现left join的嵌套很罗索，但是肯定可以转的。

你的句子写起来太长，我举个例子说明一下吧。

mssql:
a
left join b
left join c
left join d

=>Access
select * from
(select * from
(select * from
(select * from
(a left join b)as x1
left join c)as x2
left join d)as x3) as x4


希望你自己不要转晕了。