I want to generate a query that calculates the work hours of all
employees based on adding/subtracting from an 80 hour work period.

TABLES (3):

EMPLOYEE:
EmpNum    FirstName    LastName
1        John        Smith
2        Jane        Doe

CALENDAR:
Year    PayPeriod    BeginDate    EndDate
2007        1    1/1/2007    1/14/2007
2007        2    1/15/2007    1/28/2007

ATTENDANCE:
EmpNum    Date        Code    Hours
1                 1/2/2007    A    1
1        1/3/2007    T    0.5
2        1/3/2007    S    8

Code "A" means extra hours worked, "T" means Tardy, "S" means Sick.

I thought about designating "Hours" as either positive or negative,
but I think this could be very easily prone to errors during data
entry.

I want my query to display:

EmpNum    FirstName    LastName    Hours
1        John        Smith        80.5 (from 80+1-0.5)
2        Jane        Doe        72 (from 80-8)

Here is what I have for the query

SELECT E.EmpNum, E.FirstName, E.LastName,
 80-SUM(A1.Hours)-(SUM(A1.Minutes)/60)+SUM(A2.Hours)+(SUM(A2.Minutes)/
60) AS Hours

FROM Employee AS E, Calendar AS C, Attendance AS A1, Attendance AS A2

WHERE C.Year=[Insert Year] AND C.PayPeriod=[Insert PayPeriod]
  AND
         (E.EmpNum=A1.EmpNum AND A1.Date BETWEEN C.BeginDate AND
C.EndDate
          AND A1.AttendanceCode<>'A')
  OR
         (E.EmpNum=A2.EmpNum AND A2.Date BETWEEN C.BeginDate AND
C.EndDate
          AND A2.AttendanceCode='A')

GROUP BY E.EmpNum, E.FirstName, E.LastName;

But it is giving me problems, where the hours for John Smith and Jane
Done seems to get added together.  What I am doing wrong?

Thanks a lot for your help!
Alex