Timesheet report help

[Art Barnwell]

I have an Access 2002 database the stores daily time-worked records. I need to produce a “weekly timesheet report”. The report will include employee information on the left side, daily hours worked for the days of that week in the center section and weekly subtotals on the right side. I need a query that will return time records for each workday for a particular week but I have not bee successful. Any help would be appreciated.

 

[jobb]

can you tell us what your data fields are that you will be reporting on? For example, which of the following fields are in your records: time-in and time-out, date, day of the week, total hours worked per day. Also, does every record for a particular employee have the employee's name or other unique identifier?

 

[Art Barnwell]

There are several related tables most notable are tblEmployees and tblTimeSheets. Employees keeps all information related to individual employees and the prime key is EmpGID. Each employee has a unique numeric ID number. tblTimeSheets stores daily time records for all active employees. The table has a clustered primary key that includes EmpGID (a 1 to many relation to tblEmployees) and WorkDate. The clustered key insures that only one record per employee, per work date can exist.

The tblTimeSheets includes the following fields related to the needed timesheet report:
EmpGID:
WorkDate:
StartTime:
StopTime:
BreakTime:
Abs1Code:
Abs2Code:

Abs1Code and Abs2Code are used to account for vacation days and other “time-off” issues. They are not included in working time calculations but do need to be subtotaled on the right side of the report. The frmTimeSheet has a query as the data source. This query includes expression fields that provide for time calculations including “total daily working time”, “daily overtime,” “Full Name”, DayOfWeek, etc.

The report needs to have 3 sections.

The left section will include the following employee related columns:

Employee Name: (Derived in query from tblEmployees)
Employee ID: (EmpGID)(Relates to tblEmployees)
Emp OE Code: (From tblEmployees)
Norm Wkly Hours: (Static Information from tblEmployees )
No of Days Wkly: (Static Information from tblEmployees )

The center section will include daily time worked information. This includes 7 groups of columns, one group per week day. The first day of week is always Monday. Each workday column group includes 7 columns. These columns include fields from tblTimeSheets as follows:

Time In: (StartTime)
Break-Time: (BreakTime)
Time Out: (EndTime)
Time Worked: Derived in query ((EndTime less StartTime ) less BreakTime)
OT: Daily Overtime derived in query (Number of hours over 8)
Abs-1: (Abs1Code)
Abs-2: (Abs2Code)

The left section includes weekly subtotals as follows:
Total Reg Hours: (sum( (Mo_TotalHours - MtHours)+ (Tu_TotalHours – Tu_OtHours)+ (We_TotalHours - OtHours)+ (Th_TotalHours - ThOtHours)+ (Fr_TotalHours – Fr_OtHours)+ (Sa_TotalHours - Sa_OtHours)+ (Su_TotalHours - Su_OtHours))

Total OT: (Total Weekly Hours – 40)

Abs-1: Total Abs1Hours (daily Abs1Hours can be derived from tblPayCodes)

Abs-2: Total Abs2Hours (daily Abs1Hours can be derived from tblPayCodes)


The work week is displayed in the report header. I also need a mechanism for selecting a specific work week as report criteria.


I apologize for this long-winded description. If needed I can send a prototype MDE file as well as an Excel version of the report needed. If you would prefer this, my email address is eabarnwell@bellsouth.net

My current approach to solving my report problem is to create a query that includes 8 nested queries, 1 for each work day and 1 to calc subtotals. So far I have been unsuccessful in creating such a query.


THANKS FOR ANY HELP YOU ARE WILLING TO PROVIDE!!!

 

[jobb]

yikes! okay, I'm not sure I'm qualified to answer this one so if anyone wants to help out, that would be great.

To select a week, put a combo box on a form that is based on one field from your timesheets table: workdate . Under properties for your combo box, edit the row source, and adjust the query so it Groups By work date. Make the sort order ascending. Then put this in for your criteria: Weekday([date])=2. Then you should have a combo box where you can select all Mondays from your timesheets table.

For your report query, you can then include the work date criteria..

Between [forms]![dateform]![combo_weekOFmonday] and DateAdd("d",[forms]![dateform]![combo_weekOFmonday],6)

For the rest of your query, just join your employees table with your timesheet table, and group by employee and workdate, including all the extra employee info in your query.

For your report you just need to group by employee id. Then create a group header where you can include all the extra employee info. The daily totals can be unbound text boxes with formulas like you listed. Then make a group footer with your employees weekly totals in it.

I hope that helps.. ???