IF...THEN...ELSE & successive rows

[trwebster]

I have a query that sums the daily hours for each employee based upon 2 categories (Normal and Leave). Since most of these employees are salary-exempt, I need to report 7 hours total for each date unless they took leave time, which if they did I need to report how many total hours of leave time and how many normal hours.

What I have right now:

AdvocateLNFN act_date category hours
Doe, John 7/1/2006 Normal 8.2
Doe, John 7/2/2006 Normal 5.8
Doe, John 7/3/2006 Normal 3.5
Doe, John 7/3/2006 Leave 3.5
Doe, John 7/4/2006 Leave 7.0
Doe, John 7/5/2006 Normal 4.3

What I need:

AdvocateLNFN act_date category hours
Doe, John 7/1/2006 Normal 7.0
Doe, John 7/2/2006 Normal 7.0
Doe, John 7/3/2006 Normal 3.5
Doe, John 7/3/2006 Leave 3.5
Doe, John 7/4/2006 Leave 7.0
Doe, John 7/5/2006 Normal 7.0

I know how to write an expression to change any "Normal" hours to 7.0, but I don't know what I need to do to get the query to recognize when there are 2 entries for the same date.

Any suggestions would be most welcome.

Here is my SQL statement (this query is built off a main query that sums the hours for the day):

SELECT [trw Timeslip - EZ Labor].AdvocateLNFN, [trw Timeslip - EZ Labor].act_date, [trw Timeslip - EZ Labor].category, IIf([category]="Normal","7.0",[sumofhours]) AS [Total Hours]
FROM [trw Timeslip - EZ Labor]
GROUP BY [trw Timeslip - EZ Labor].AdvocateLNFN, [trw Timeslip - EZ Labor].act_date, [trw Timeslip - EZ Labor].category, IIf([category]="Normal","7.0",[sumofhours])
ORDER BY [trw Timeslip - EZ Labor].AdvocateLNFN, [trw Timeslip - EZ Labor].act_date;

 

[PHV]

You may try something like this (typed, untested):
SELECT A.AdvocateLNFN, A.act_date, A.category
, IIf(A.category='Normal' AND B.category Is Null,'7.0',A.sumofhours) AS [Total Hours]
FROM [trw Timeslip - EZ Labor] AS A LEFT JOIN (
SELECT AdvocateLNFN, act_date FROM [trw Timeslip - EZ Labor] WHERE category = 'Leave'
) AS B ON A.AdvocateLNFN = B.AdvocateLNFN AND A.act_date = B.act_date

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[trwebster]

Thanks. It seems to work fine, but now I have another question. What would I need to use if instead of returning the actual hours worked on days that also have Leave time I wanted instead to return 7.0 minus the Leave time? After I ran your query I found that several of the staff put in 7 hours of Leave and also "normal" hours on the same day or they put in 3.5 hours of leave and only 3 hours of normal, so it doesn't add up to 7.0.

Would another IIf statement work? Maybe IIf(b.category Is Not Null, ('7.0'-a.sumofhours)