请教大家一个关于多表连接的问题 [复制链接]

请教大家一个关于多表连接的问题：

在ACCESS数据库里共有四个表：
Employee记录员工信息，
Attendance记录考勤，
Output记录产量，
Rejection记录返工次数。

我的意图是在一个查询表里加总统计出某一时间段的各项情况，包括考勤，产量及返工次数等。
我的SQL语句如下，但出来的结果完全不对，好像累加了几次后的结果。
本人对SQL只有一些表面粗略的了解，麻烦大家帮忙看一下。谢谢。

SELECT    e.EmployeeID AS 工號,
      e.ChineseName AS 姓名,
      e.EnglishName AS 英文名,
      sum(a.OvertimeDuration) AS 加班,
      sum(a.LeaveDuration) AS 请假,
      sum(o.OutputA) AS 产量A,
      sum(o.OutputB) AS 产量B,
      sum(o.OutputC) AS 产量C,
      sum(r.Quantity) AS 返工次数

FROM ((Employee e INNER JOIN Attendance a ON e.EmployeeID = a.EmployeeNo)
                                 INNER JOIN Output o ON e.System = o.SystemID)
                                 INNER JOIN Rejection r ON e.System = r.System

WHERE a.ApplyDate BETWEEN '2012-01-01' AND '2012-12-31' AND
             o.WorkDate  BETWEEN '2012-01-01' AND '2012-12-31' AND
             r.SubmmitDate BETWEEN '2012-01-01' AND '2012-12-31'

GROUP BY e.EmployeeID,
                   e.ChineseName,
                   e.EnglishName

各位走过路过的SQL高手，大家帮帮忙啦，谢谢!

试了Access里自动产生的代码，还是不能解决这个问题，算出的数还是重复计算的。纠结还几天了
（以下代码只连接了3个表）


SELECT e.EmployeeID,

               e.ChineseName,
               e.EnglishName,
               e.BrsUserName,
               Sum(o.IndividualMarker) AS [IndividualMarker 之 总计],
               Sum(o.CombineMarkerByGroup) AS [CombineMarkerByGroup 之 总计],
               Sum(o.CombineMarkerByStyle) AS [CombineMarkerByStyle 之 总计],
               Sum(r.Quantity) AS [Quantity 之 总计]


FROM (Employee AS e INNER JOIN Rejection AS r ON e.[BrsUserName] = r.[BrsID])
               INNER JOIN [Output] AS o ON (o.BrsID = r.BrsID) AND (e.[BrsUserName] = o.[BrsID])


GROUP BY e.EmployeeID, e.ChineseName, e.EnglishName, e.BrsUserName;

请看下图我的要求：

图片:Tables.png

我最可依赖的论坛却没人理我，自己修炼吧。
已经有点眉目了，要用到子查询，然后再连接。噢耶！

看来SQL方面还要下功夫学习学习，
这个简单的问题纠结了我将近一个星期。

你没说清楚，我也不好回答，你如果喜欢数据库的话，可以跟我联系。QQ:446844655  网名：你是我的兄弟 。我熟悉 MySQL

谢谢楼上kisscolo 的关注。可能是我没有把问题描述清楚。
以后有问题一定向你请教。 谢谢。

以下是完整的SQL语句，供遇到同样问题的朋友参考

SELECT e.EmployeeID AS 工號,
       e.ChineseName AS 姓名,
       e.EnglishName AS 英文名,
       e.Position AS 职位,
       a.AOT AS [加班(小时)],
       a.ALV AS [请假(小时)],
       d.DWKD AS 应上班天数,
       ROUND((应上班天数*9 + IIF(ISNULL([加班(小时)]),0,[加班(小时)]) - IIF(ISNULL([请假(小时)]),0,[请假(小时)]))/9,2) AS 实际上班天数,
       IIF(ISNULL(o.OIM),0,o.OIM) + IIF(ISNULL(o.OCMS),0,o.OCMS) AS [总产量(款)],
       ROUND([总产量(款)]/实际上班天数,1) AS [日均产量(款)],
       r.RJ AS [返工数量(款)],
       ROUND([返工数量(款)]/[总产量(款)]*100,2) AS [返工率(%)]

FROM  (((Employee e

LEFT JOIN
       (SELECT EmployeeNo,SUM(OvertimeDuration) AS AOT,SUM(LeaveDuration) AS ALV
       FROM Attendance
       WHERE DurationFrom >= 'DATEFROM' AND DurationTo <= 'DATETO'
       GROUP BY EmployeeNo) a
       ON e.EmployeeID = a.EmployeeNo)

LEFT JOIN
     (SELECT m.EmployeeID AS DEMPID, COUNT(w.WorkDate) AS DWKD
      FROM Employee m, Demand w
      WHERE w.WorkDate BETWEEN IIF(m.StartDate > 'DATEFROM', m.StartDate, 'DATEFROM') AND
                               (IIF(m.LeaveDate IS NULL OR m.LeaveDate = '', 'DATETO', IIF(m.LeaveDate < 'DATETO', m.LeaveDate,'DATETO' )))
              AND (w.Remark IS NULL OR w.Remark ='')
      GROUP BY m.EmployeeID) d
      ON d.DEMPID = e.EmployeeID)

LEFT JOIN
     (SELECT BrsID,SUM(IndividualMarker) AS OIM,SUM(CombineMarkerByStyle) AS OCMS
     FROM Output
     WHERE WorkDate BETWEEN 'DATEFROM' AND 'DATETO'
     GROUP BY BrsID) o
     ON e.BrsUserName = o.BrsID)

LEFT JOIN
     (SELECT BrsID,SUM(Quantity) AS RJ
     FROM Rejection
     WHERE DurationFrom >= 'DATEFROM' AND DurationTo <= 'DATETO'
     GROUP BY BrsID) r
     ON e.BrsUserName = r.BrsID

ORDER BY ORDERNUMBER ORDERTYPE