VB6 ms访问sql查询不同计数两个表

Question

我需要为每个特定员工输出此数据： employee_Id，employee_name，presence.year，presence.month，count（day），sum（working hours），sum（holiday）小时），[工作时间] - [假期时间]。 我用VB6项目这个查询，但似乎没有很好的工作：

SELECT 
    Employees.Employee_ID AS ID 
    , Employees.Full_Name AS Name 
    , Presence.Yr AS Year 
    , Presence.Mnth AS Month 
    , SUM(IIF(Presence.Dy, 1, 0)) Work_Days 
    , SUM(IIF(Presence.Hr, Presence.Hr, 0)) AS Work_Hours 
    , SUM(IIF(Presence.Holidays, Presence.Holidays, 0)) AS Holidays_Hours 
    , SUM(IIF(Presence.Hr, Presence.Hr, 0)) - SUM(IIF(Presence.Holidays, Presence.Holidays, 0) AS Total_Work_Hours 
FROM Employees 
INNER JOIN Presence ON Presence.Employee_ID = Employees.Employee_ID 
WHERE Employees.Company = 'aCompany' 
    AND Employees.Employee_ID = 'anEmmployee' 
GROUP BY Employees.Employee_ID, Employees.Full_Name, Presence.Yr, Presence.Mnth 

这样在图片：http://oi60.tinypic.com/122c6xz.jpg

任何人都可以在这方面帮助？

Answer 1

下面是一些调整，请查询：

SELECT 
    Employees.Employee_ID AS ID 
    , Employees.Full_Name AS Name 
    , Presence.Yr AS Year 
    , Presence.Mnth AS Month 
    , SUM(IIF(Presence.Dy, 1, 0)) Work_Days 
    , SUM(IIF(Presence.Hr, Presence.Hr, 0)) AS Work_Hours 
    , SUM(IIF(Presence.Holidays, Presence.Holidays, 0)) AS Holidays_Hours 
    , SUM(IIF(Presence.Hr, Presence.Hr, 0)) - SUM(IIF(Presence.Holidays, Presence.Holidays, 0) AS Total_Work_Hours 
    , SUM(Presence.Hr)/Employees.HoursPerDay + Employees.TotalAnnualHolidays - Employees.DoneHolidays AS AvailableHolidays 
FROM Employees 
INNER JOIN Presence ON Presence.Employee_ID = Employees.Employee_ID 
WHERE Employees.Company = 'aCompany' 
    AND Employees.Employee_ID = 'anEmmployee' 
GROUP BY Employees.Employee_ID, Employees.Full_Name, Employees.HoursPerDay, Employees.TotalAnnualHolidays, Employees.DoneHolidays, Presence.Yr, Presence.Mnth 

您可以测试这个新的查询，并给我讲的结果和你在找什么区别的信息？

希望这会有所帮助。