Query week to date, month to date, year to date hours

G

Guest

I am wondering how i can query my data so thatit retreived a total sum of
hours worked for a specific time frame. I have a query connecting Employee
table with WatchHours table. I want to create a query that retreives their
LastName, FirstName, Title, ID from the Employee table, and OvertimeHours for
a specific time period from the WatchResults Table.

I want to be able to calculate week to day, month to date, quarter to date,
and year to date total hours worked in a report.

Is this just a matter of setting the criteria in my query for overtime
hours. By the way, DateWorked is where i retreive dates.
 
D

Duane Hookom

You can create a totals query
SELECT LastName, FirstName, TItle, ID,
Sum(abs(Year(Date())=Year([DateWorked])) * [OverTimeHours]) as OTYTD,
Sum(abs(Format(Date(),"yyyymm")=Format([DateWorked],"yyyymm")) *
[OverTimeHours]) as OTMTD,
**** You Should be able to figure the other columns *****
FROM ....
GROUP BY LastName, FirstName, TItle, ID
Expressions like abs(Format(Date(),"yyyymm")=Format([DateWorked],"yyyymm"))
will return 1 for true values or 0 for non-true values.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top