S
Sammo
Unregistered / Unconfirmed
GUEST, unregistred user!
有一个关于ACCESS的怪事！
我已经给它搞得头痛了，现在把它贴出来，请各位老大帮忙看看！！
请看这里：
http://sammo.51.net/Matisa.mdb
里边有一个GoodsDetails的表！
我已经写好MonthIntoTotal和MonthOutoTotal两个视图
现在想从这两个视图中现进行查询
所以就写了MonthOnlyInto、MonthOnlyOuto和MonthBoth三个Sql语句！
但前面两个出错！后面那个得出的结果也不对！！
请帮忙看看！！谢谢！！：）
GoodsDetails表的结构如下：
DetailDate,GoodsCode,GoodsName,Epitome,Amount,UnitPrice,TotalMoney
MonthIntoTotal视图的SQL语句如下（该语句是对的！得出的结果也是对的）：
SELECT year([DetailDate]) AS TheYear, month([DetailDate]) AS TheMonth,
[GoodsCode] AS TheGoodsCode, [GoodsName] AS TheGoodsName,
sum([Amount]) AS ttlIntoAmount, sum([TotalMoney]) AS ttlIntoMoney
FROM GoodsDetails
WHERE Epitome='进货'
GROUP BY [GoodsCode], [GoodsName], year([DetailDate]), month([DetailDate]);
MonthOutoTotal视图的SQL语句如下（该语句是对的！得出的结果也是对的）：
SELECT year([DetailDate]) AS TheYear, month([DetailDate]) AS TheMonth,
[GoodsCode] AS TheGoodsCode, [GoodsName] AS TheGoodsName,
sum([Amount]) AS ttlOutoAmount, sum([TotalMoney]) AS ttlOutoMoney
FROM GoodsDetails
WHERE Epitome='出货'
GROUP BY [GoodsCode], [GoodsName], year([DetailDate]), month([DetailDate]);
MonthOnlyInto视图的SQL语句如下（出错，提示No Current Record）：
SELECT [TheYear], [TheMonth], A.TheGoodsCode AS GoodsCode, A.TheGoodsName AS GoodsName,
A.ttlIntoAmount AS IntoAmount, A.ttlIntoMoney AS IntoMoney, 0 AS OutoAmunt,
0 AS OutoMoney, 0 AS Cost, 0 AS Profit
FROM MonthIntoTotal AS A
WHERE A.TheGoodsCode NOT IN (SELECT DISTINCT TheGoodsCode FROM MonthOutoTotal);
MonthOnlyOuto视图的SQL语句如下（出错，提示No Current Record）：
SELECT [TheYear], [TheMonth], a.TheGoodsCode AS GoodsCode, a.TheGoodsName AS GoodsName,
0 AS IntoAmount, 0 AS IntoMoney, a.ttlOutoAmount AS OutoAmunt, a.ttlOutoMoney AS OutoMoney,
0 AS Cost, 0 AS Profit
FROM MonthOutoTotal AS a
WHERE a.TheGoodsCode NOT IN (SELECT DISTINCT TheGoodsCode FROM MonthIntoTotal);
MonthBoth视图的SQL语句如下（没有出错，但得出的结果不对，有重复的结果）
SELECT A.TheYear, A.TheMonth, A.TheGoodsCode AS GoodsCode, A.TheGoodsName AS GoodsName,
A.ttlIntoAmount AS IntoAmount, A.ttlIntoMoney AS IntoMoney, B.ttlOutoAmount AS OutoAmunt,
B.ttlOutoMoney AS OutoMoney, 0 AS Cost, 0 AS Profit
FROM MonthIntoTotal AS A RIGHT JOIN MonthOutoTotal AS B ON A.TheGoodsCode=B.TheGoodsCode;
我已经给它搞得头痛了，现在把它贴出来，请各位老大帮忙看看！！
请看这里：
http://sammo.51.net/Matisa.mdb
里边有一个GoodsDetails的表！
我已经写好MonthIntoTotal和MonthOutoTotal两个视图
现在想从这两个视图中现进行查询
所以就写了MonthOnlyInto、MonthOnlyOuto和MonthBoth三个Sql语句！
但前面两个出错！后面那个得出的结果也不对！！
请帮忙看看！！谢谢！！：）
GoodsDetails表的结构如下：
DetailDate,GoodsCode,GoodsName,Epitome,Amount,UnitPrice,TotalMoney
MonthIntoTotal视图的SQL语句如下（该语句是对的！得出的结果也是对的）：
SELECT year([DetailDate]) AS TheYear, month([DetailDate]) AS TheMonth,
[GoodsCode] AS TheGoodsCode, [GoodsName] AS TheGoodsName,
sum([Amount]) AS ttlIntoAmount, sum([TotalMoney]) AS ttlIntoMoney
FROM GoodsDetails
WHERE Epitome='进货'
GROUP BY [GoodsCode], [GoodsName], year([DetailDate]), month([DetailDate]);
MonthOutoTotal视图的SQL语句如下（该语句是对的！得出的结果也是对的）：
SELECT year([DetailDate]) AS TheYear, month([DetailDate]) AS TheMonth,
[GoodsCode] AS TheGoodsCode, [GoodsName] AS TheGoodsName,
sum([Amount]) AS ttlOutoAmount, sum([TotalMoney]) AS ttlOutoMoney
FROM GoodsDetails
WHERE Epitome='出货'
GROUP BY [GoodsCode], [GoodsName], year([DetailDate]), month([DetailDate]);
MonthOnlyInto视图的SQL语句如下（出错，提示No Current Record）：
SELECT [TheYear], [TheMonth], A.TheGoodsCode AS GoodsCode, A.TheGoodsName AS GoodsName,
A.ttlIntoAmount AS IntoAmount, A.ttlIntoMoney AS IntoMoney, 0 AS OutoAmunt,
0 AS OutoMoney, 0 AS Cost, 0 AS Profit
FROM MonthIntoTotal AS A
WHERE A.TheGoodsCode NOT IN (SELECT DISTINCT TheGoodsCode FROM MonthOutoTotal);
MonthOnlyOuto视图的SQL语句如下（出错，提示No Current Record）：
SELECT [TheYear], [TheMonth], a.TheGoodsCode AS GoodsCode, a.TheGoodsName AS GoodsName,
0 AS IntoAmount, 0 AS IntoMoney, a.ttlOutoAmount AS OutoAmunt, a.ttlOutoMoney AS OutoMoney,
0 AS Cost, 0 AS Profit
FROM MonthOutoTotal AS a
WHERE a.TheGoodsCode NOT IN (SELECT DISTINCT TheGoodsCode FROM MonthIntoTotal);
MonthBoth视图的SQL语句如下（没有出错，但得出的结果不对，有重复的结果）
SELECT A.TheYear, A.TheMonth, A.TheGoodsCode AS GoodsCode, A.TheGoodsName AS GoodsName,
A.ttlIntoAmount AS IntoAmount, A.ttlIntoMoney AS IntoMoney, B.ttlOutoAmount AS OutoAmunt,
B.ttlOutoMoney AS OutoMoney, 0 AS Cost, 0 AS Profit
FROM MonthIntoTotal AS A RIGHT JOIN MonthOutoTotal AS B ON A.TheGoodsCode=B.TheGoodsCode;
