ACCESS连接查询的问题

[muwenyi]

请各位专家帮忙看一下!万分感谢
SELECT a.ClassName, a.MasterName, a.SubName, SUM([b.Qty]), SUM([c.Qty])
FROM (StockList AS a LEFT JOIN [SELECT ClassName,MasterName,SubName FROM StockList WHERE IfInstock = true] AS b ON a.SubName=b.SubName)
LEFT JOIN [SELECT ClassName, MasterName,SubName FROM stocklist WHERE IfInstock = false] AS c ON a.SubName=c.SubName
GROUP BY a.ClassName, a.MasterName, a.SubName;

为什么会提示Microsoft Jet 找不到输入表或查询'SELECT ClassName,MasterName,SubName FROM StockList WHERE IfInstock = true'
可是在我的StockList表中上述字段没有任何错误,将此句单提出来查询结果没有问题.

[superjj2002]

不好意思，ACESS不熟悉。

SELECT a.ClassName, a.MasterName, a.SubName, SUM([b.Qty]), SUM([c.Qty])
FROM StockList AS a LEFT JOIN [SELECT ClassName,MasterName,SubName FROM StockList WHERE IfInstock = true] AS b ON a.SubName=b.SubName
LEFT JOIN [SELECT ClassName, MasterName,SubName FROM stocklist WHERE IfInstock = false] AS c ON a.SubName=c.SubName
GROUP BY a.ClassName, a.MasterName, a.SubName;

这样呢？（去掉括号，好像就对了，呵呵）

[Yang_]

能对吗？！b,c两个都没有Qty字段！

[superjj2002]

(SELECT a.ClassName, a.MasterName, a.SubName, SUM([b.Qty]), SUM([c.Qty])
FROM StockList AS a LEFT JOIN [SELECT ClassName,MasterName,SubName FROM StockList WHERE IfInstock = true] AS b ON a.SubName=b.SubName)
LEFT JOIN [SELECT ClassName, MasterName,SubName FROM stocklist WHERE IfInstock = false] AS c ON a.SubName=c.SubName
GROUP BY a.ClassName, a.MasterName, a.SubName;

这样行不行？

[muwenyi]

TO superjj2002(我真的很菜！) (
这样不可以,会提示错误:联合查询语法错误

[Xsean]

你的句子写的乱七八糟，说说你要实现什么结果，我帮你写吧

[muwenyi]

请各位专家帮忙看一下可以吗?

[muwenyi]

为什么统计出来的结果全都不正确呢?没有人回答了吗?

[playmud]

什么叫统计出入库的数量？

[Xsean]

测试通过，拿分来

SELECT a.ClassName,a.MasterName,a.SubName ,SUM([a.TQ1]) AS TQ1, SUM([b.TQ2]) AS TQ2
from [SELECT ClassName,MasterName,SubName,sum(Qty) as TQ1 FROM StockList WHERE IfInstock = true GROUP BY ClassName,MasterName,SubName]. as a
left join [SELECT ClassName,MasterName,SubName,sum(Qty) as TQ2 FROM StockList WHERE IfInstock = false GROUP BY ClassName,MasterName,SubName]. as b
on a.MasterName=b.MasterName and a.SubName=b.SubName
GROUP BY a.ClassName, a.MasterName, a.SubName

[muwenyi]

StockList 表:
StockDate:日期
MasterName:材料名称
SubName:型号
Qty:数量
ClassName :材料类别
IfInstock:是否类型.1表示入库,0表示出库
--------------------------------------------------------------------
RecordID StockDate MasterName SubName Qty ClassName IfInstock
1 02-5-1 合尺 3.5m 2 工具 1
2 02-4-14 合尺 3.5m 1 工具 1
3 02-4-14 镀钛不锈钢板 0.8*4*7 2 板材 1
4 02-4-14 镀钛不锈钢板 0.8*5*6 3 板材 1
5 02-4-14 合尺 3.5m 1 工具 0
6 02-4-14 镀钛不锈钢板 0.8*5*6 2 板材 1
6 02-4-14 镀钛不锈钢板 0.8*5*6 2 板材 0
--------------------------------------------------------------------
查询统计结果:
####################################################################
ClassName MasterName SubName Qty1(入库数量) Qty2(出库数量)
工具 合尺 3.5m 3 1
板材 镀钛不锈钢板 0.8*4*7 2
板材 镀钛不锈钢板 0.8*5*6 5 2
####################################################################
这就是我需要的查询统计结果
谢谢各位专家帮忙

[playmud]

select x.classname,x.MasterName,x.SubName,sum(x.Qty1) as qty1,sum(y.qty2) as qty2
from [select * from StockList where ifinstock=1]. as X
left join [select * from StockList where ifinstock= 0]. as Y
on x.RecordID= y.RecordID group by subname

[muwenyi]

to playmud(观望~淡漠~孤傲) :
SELECT x.classname, x.MasterName, x.SubName, sum(x.Qty) AS qty1, sum(y.qty) AS qty2
FROM [select RecordID,classname,mastername,subname,Qty from StockList where ifinstock=true]. AS X LEFT JOIN [select RecordID,classname,mastername,subname,Qty from StockList where ifinstock=false]. AS Y ON x.RecordID= y.RecordID
GROUP BY x.classname, x.mastername, x.subname
您的这种方法只能统计出入库的数量.

我在各位的帮助下是这样写的:
SELECT a.ClassName, a.MasterName, a.SubName, SUM([b.Qty]) AS TQ1, SUM([c.Qty]) AS TQ2
FROM (StockList AS a LEFT JOIN [SELECT ClassName,MasterName,SubName,Qty FROM StockList WHERE IfInstock = true]. AS b ON a.SubName=b.SubName) LEFT JOIN [SELECT ClassName, MasterName,SubName,Qty FROM stocklist WHERE IfInstock = false]. AS c ON a.SubName=c.SubName
GROUP BY a.ClassName, a.MasterName, a.SubName;
但是统计出的入库和出库的数量都不正确,不知道我的问题出在什么地方?

[muwenyi]

TO xsean() :
非常感谢您,我这就把分给您.但是有些不明白,既然在第一个虚表中已经使用了GROUP BY统计出了入库数,为什么在外层又会使用GROUP BY a.ClassName, a.MasterName, a.SubName并且结果正确呢?

[muwenyi]

奇怪,我问题的分数已经给出去了呀，为什么还是这个样子?
管理中已经看了给出了分,为什么这个问题还算没有解决的问题呢?
TO xsean() :
如果您发现您的专家分没有曾加,我再去开一个帖子给您100分!
非常感谢您的帮助!