Attendance Query

[alexcuse]

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

 

[Dale Fye]

Alex,

Untested, but this should work. Notice that I have replaced your [Date] and
[Year] fields with AttDate and CalYear. These (Date, Year) are both reserved
words in Access and should not be used as field names.

SELECT E.EmpNum, E.FirstName, E.LastName, 80+A1.Delta
FROM Employee E
LEFT JOIN
(SELECT A.EmpNum, SUM(IIF(

 = "A", 1, -1) * [Hours]) as Delta
FROM Attendance A INNER JOIN Calendar C
ON A.AttDate BETWEEN C.BeginDate AND C.EndDate
WHERE C.CalYear=[Insert Year] AND C.PayPeriod=[Insert PayPeriod]
GROUP BY A.EmpNum) as A1
ON E.EmpNum = A1.EmpNum

The subquery joins the Attendance and Calendar tables and then filters for
only the year and payperiod values you enter when you run the query.

HTH
Dale

 

[alexcuse]

Thanks for all your help Dale! It worked perfectly, and it even
included names that from the Employee table that wasn't in the
Attendance table (which was what I wanted). I will definitely try to
learn from your usage of IIF and JOIN with inner selects in designing
other queries. Thanks also for the syntax correction regarding Date
and Year, I will check the other tables to make sure they don't have
bad naming.

Thanks a lot,
-Alex

Alex,

Untested, but this should work. Notice that I have replaced your [Date] and
[Year] fields with AttDate and CalYear. These (Date, Year) are both reserved
words in Access and should not be used as field names.

SELECT E.EmpNum, E.FirstName, E.LastName, 80+A1.Delta
FROM Employee E
LEFT JOIN
(SELECT A.EmpNum, SUM(IIF(

 = "A", 1, -1) * [Hours]) as Delta
FROM Attendance A INNER JOIN Calendar C
ON A.AttDate BETWEEN C.BeginDate AND C.EndDate
WHERE C.CalYear=[Insert Year] AND C.PayPeriod=[Insert PayPeriod]
GROUP BY A.EmpNum) as A1
ON E.EmpNum = A1.EmpNum

The subquery joins the Attendance and Calendar tables and then filters for
only the year and payperiod values you enter when you run the query.

HTH
Dale

--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

[QUOTE="alexcuse"]
I want to generate a query that calculates the work hours of all
employees based on adding/subtracting from an 80 hour work period.[/QUOTE]
[QUOTE]
TABLES (3):[/QUOTE]
[QUOTE]
EMPLOYEE:
EmpNum     FirstName       LastName
1          John            Smith
2          Jane            Doe[/QUOTE]
[QUOTE]
CALENDAR:
Year       PayPeriod       BeginDate       EndDate
2007               1       1/1/2007        1/14/2007
2007               2       1/15/2007       1/28/2007[/QUOTE]
[QUOTE]
ATTENDANCE:
EmpNum     Date            Code    Hours
1                  1/2/2007        A       1
1          1/3/2007        T       0.5
2          1/3/2007        S       8[/QUOTE]
[QUOTE]
Code "A" means extra hours worked, "T" means Tardy, "S" means Sick.[/QUOTE]
[QUOTE]
I thought about designating "Hours" as either positive or negative,
but I think this could be very easily prone to errors during data
entry.[/QUOTE]
[QUOTE]
I want my query to display:[/QUOTE]
[QUOTE]
EmpNum     FirstName       LastName        Hours
1          John            Smith           80.5 (from 80+1-0.5)
2          Jane            Doe             72 (from 80-8)[/QUOTE]
[QUOTE]
Here is what I have for the query[/QUOTE]
[QUOTE]
SELECT E.EmpNum, E.FirstName, E.LastName,
80-SUM(A1.Hours)-(SUM(A1.Minutes)/60)+SUM(A2.Hours)+(SUM(A2.Minutes)/
60) AS Hours[/QUOTE]
[QUOTE]
FROM Employee AS E, Calendar AS C, Attendance AS A1, Attendance AS A2[/QUOTE]
[QUOTE]
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')[/QUOTE]
[QUOTE]
GROUP BY E.EmpNum, E.FirstName, E.LastName;[/QUOTE]
[QUOTE]
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?[/QUOTE]
[QUOTE]
Thanks a lot for your help!
Alex[/QUOTE][/QUOTE]