TimeSheet table to Weekly Report

[Breanna]

I am using Access 2013 and I need to be able to generate a weekly report based on a time sheet table. I am not sure how to do this or how to even begin.
Below is sample data from my table:



EmplyNum
Date
TimeIn1
TimeOut1
SiteName
Descrition
RateCode
WeekEnd
PayPeriod


5678
12/22/2014
6:00 AM
8:30 AM

ground
0608-04
12/28/14
Dec 22- Jan 4


5678
12/22/2014
8:30 AM
12:00 PM

ground
0608-04
12/28/14
Dec 22- Jan 4


5678
12/22/2014
12:30 PM
3:00 PM

Ground
0608-04
12/28/14
Dec 22- Jan 4













5678
12/23/2014
6:00 AM
8:30 AM

office
4909-00
12/28/14
Dec 22- Jan 4


5678
12/23/2014
8:30 AM
10:00 AM
SE05051A
office
4909-00
12/28/14
Dec 22- Jan 4
























5678
12/24/2014
12:15 AM
8:15 AM

vacation

12/28/14
Dec 22- Jan 4













5678
12/25/2014
12:15 AM
8:15 AM

Holiday

12/28/14
Dec 22- Jan 4













5678
12/26/2014
12:15 AM
8:15 AM

Vacation

12/28/14
Dec 22- Jan 4




Below is the report example I need the data to go into:








PAY PERIOD






START DATE:







END DATE:





















CHECK DATE:







PAYROLL #
Week








PAY
DEDUCTIONS


EMPLOYEE NAME
PAY
PAC
REG
OVT
DOUBLE
VAC
HOL








RATE
CODE
HRS
HRS
HRS
HRS
HRS






Employee 1



























































Employee 2



























































Employee 3





























































As you can see the report will only have totals on it and not any dates or times (other than week end) Also due to the need to have specific hours for the rate code employees may have multiple entries per day. I am not sure what other information is need. I am just at a loss on how to even start something like this. I mean do I start with a query? or go straight to the report? How to I get Access to do the calculations per week per rate code? is this even possible? Anything helps. Mostly I need a place to start.

Thank you in advance for even stopping to read this long post and for any help, ideas, information etc that you can give. Please note though when helping I am very much new when it comes to access.

[Breanna]

I just noticed my examples show alot of blank space. I apologize! I do not know how to fix that. :/

[bbotzong]

How does your state calculate overtime? Is it > 40 hours in one workweek? Is it > 8 hours in a day? What is the criteria for doubletime?

[Breanna]

Oh gosh I am sorry, that is kind of important information.
anything over 40 hours per week is time and half.
And I know my example shows a section for double time but I am not worried about that part yet. my main focus is the over time. Thanks :)

[Bob Phillips]

Why don't you post an example workbook, maybe with a couple of manually worked examples; that table is very hard to figure.

[jonh]

Not tested but might help to get you started.

Filter data by date and convert time to minutes...

[qryTimeInMins]

SELECT EmplyNum, Date, Sum(Hour(CDate([timeout1]-[timein1]))*60+Minute(CDate([timeout1]-[timein1]))) AS Mins, Descrition
FROM Timesheet
GROUP BY EmplyNum, Date, Descrition
HAVING (((Date) Between #12/22/2014# And #12/26/2014#))

Create a cross tab query to convert 'description' fields to columns...
Set column headings, otherwise the fields are dynamic and you can't refer to them in other queries.

[qrylTimeTotal]

TRANSFORM Sum(Mins) AS Total
SELECT EmplyNum
FROM qryTimeInMins
GROUP BY EmplyNum
PIVOT Descrition In ("ground","vacation","holiday")

Convert to hours and minutes and work out the overtime...


SELECT
EmplyNum,
([ground]-[ground] Mod 60)/60 AS reghrs,
[ground] Mod 60 AS regmins,
([vacation]-[vacation] Mod 60)/60 AS vachrs,
[vacation] Mod 60 AS vacmins,
([holiday]-[holiday] Mod 60)/60 AS holhrs,
[holiday] Mod 60 AS holmins,
IIf((([ground]-[ground] Mod 60)/60)-40>0,(([ground]-[ground] Mod 60)/60)-40,0) AS ovthrs,
IIf(((([ground]-[ground] Mod 60)/60)-40)>0,[ground] Mod 60,0) AS ovtmins
FROM qrylTimeTotal

[Breanna]

Not tested but might help to get you started.



Thanks, I will try what you suggested. :)