Timesheet access Project

[aucho22]

Hello fellows,

Like many of us here, I am looking for some guidance, please note I am fairly new to Access data base...

I have the following project that works as a timesheet manager:

Users enter in a form the employee name/date/hours worked/role/overtime (yes or no)/rate... on a daily basis. At the end of every week, they must create a report depending on the dates given in a query.

I have only one table (tbl_EmployeeHours) and a query to calculate the number of hours between two dates.

At the moment a report gives me all the information above (employee name/date hours worked etc..) but what I am trying to is a bit different.

I need Access to create a report (based on a date range given by the user) that would give me: per unique name, the number of normal hours worked in total, next column, the number of hours worked as overtime (rate 1.5), then, the number of hours worked as overtime (rate 2).

This means there are 3 different totals. For example, in the date range given, someone might appear twice but in the report he will appear only once with 3 different totals...

My guess:

I would say I need to create a different query for the 3 different totals (normal hours, overtime 1.5 and overtime 2). For each query you would need to ask the user to input the date range (which would not be so user-friendly I agree...). Then, I am not sure how access would create that list of names and combine the 3 different results from the queries...?

Any sort of guidance is really appreciated!

Thank you,

Aucho22

 

[DavidAtWork]

I can't visualise your table structure and whether you enter multiple records for hours worked depending on the rate or whether your tbl_EmployeeHours has separate fields for the different rates and number of hours worked in each.
If the former, I think you'd need to think of 3 different reports which you can combine into one report using sub-reports.
Each sub-report would run off a different query with the date range parameters determined by your user input form and rate type.
If the latter then you should be able to build just one report, driven by one query with a sum for each of the rate types based on date range parameters determined by your user input form.

David

 

[aucho22]

Hi David,

Thanks for the reply,

Sorry for the lack of clearance!
My table is as such:

tbl_EmployeeHours
Date/Time Start
Date/Time End
Firstname
Lastname
Role
Rate (list: 1 (normal hours), 1.5 (overtime), 2 (overtime))
Overtime (yes or no)

Query is the same with a calculated field (elpased time between Date/Time Start and Date/Time End):
NoHours

The report would only display per unique name, within the date range given by the user, the number of normal hours worked, number of hours overtime (1.5) and number of hours overtime (2)...

To give an example:

12/06/2014 12:00:00 to 12/06/2014 14:00:00 Steve worked as normal hours (1)
12/06/2014 14:00:00 to 12/06/2014 16:00:00 Steve worked as overtime (1.5)
14/06/2014 10:00:00 to 14/06/2014 16:00:00 Steve worked as normal hours (1)

12/06/2014 10:00:00 to 12/06/2014 14:00:00 Peter worked as normal hours (1)
12/06/2014 08:00:00 to 12/06/2014 10:00:00 Peter worked as overtime (2)
14/06/2014 12:00:00 to 14/06/2014 16:00:00 Peter worked as normal hours (1)

What the report would give me: (if date range between 12/06/2014 and 14/06/2014)
Name-------Normal Hours---------Overtime 1.5---------Overtime 2
Steve------------8--------------------2-------------------0
Peter-------------8--------------------0-------------------2

Not sure it helps...?

Thank you!
Aucho 22

 

[BlueIshDan]

At work we use:

Week(Date),
type_of_hours(vacation, over_time#, towp(time_off_with_pay)),
charge_code(project/SAP/personal),
approver(user_id/name)
etc.

 

[aucho22]

Hi BlueishDan,

Thanks for your reply, unfortunately I am not sure how that can apply to what I am intending to do?

I have little knowledge of Access apart from the basics (tables, reports, forms, queries, relationships), so maybe I am missing something important?

Aucho22

 

[BlueIshDan]

Sorry I read through your post very quickly and simply threw some table fields at you that you may want to consider adding to your system.

 

[GinaWhipp]

Your table structure is part of the problem. That being said I'd try using four queries...

Make three seperate Group and Totals queries...

qryRegularHours
qryOvertime15
qryOvertime2

...take the queries from above and put in the fourth query joining on FirstName, LastName

Then use the fourth one as the basis for your report.

 

[aucho22]

Hi Gina Whipp and thank you,

I have done 3 queries: one that displays regular hours only, the next one overtime15 only and last one overtime2 only.

I will take the risk to sound silly... but I am not sure what other criteria I input in these 3 queries? (I am not familiar with groups and totals...yet)

I understand then how the fourth query comes in handy...

Thank you,
Aucho22

 

[GinaWhipp]

When creating a query there is a button, it says *Totals*. Click it and Group on names but select *Sum* for the field that holds the hours.

 

[aucho22]

Right, I think I am in the right direction.

I created the 3 queries (RegHours, Ov1,Ov2) from the very first query that calculates the elapsed time between two dates/times (Qry_Calc). This is working, giving me unique names with the sum of hours for each query.

I tried creating the fourth query but it is giving me weird stuff: duplicated names and wrong totals.

I probably do something wrong: I took the fields Firstname and Lastname from Qry_Calc, and the 3 fields "SumNoHours" from the 3 queries created above (RegHours, Ov15,Ov2). I did not group anything or put any criteria...

Any idea what my beginner brain is doing wrong? My guess is I should not take the fields Firstname and Lastname from Qry_Calc...

Thank you!
Aucho22

 

[GinaWhipp]

For the fourth query what fields are you connecting the tables on?

 

[aucho22]

For the fourth query I use:

First name and Last name from Qry_Calc
SumNoHours from query RegHours
SumNoHours from query Ov15
SumNoHours from query Ov2

Without any criteria.

Maybe I need to use directly the fields First name and Last name from one of the last 3 queries? But I was afraid by doing so, not all of the names would appear...

 

[GinaWhipp]

You have to link by something or your query is going to run wild. Are all the records showing in the RegHours query?

 

[aucho22]

Yes everything needed is showing in these 3 queries:

SumNoHours from query RegHours
SumNoHours from query Ov15
SumNoHours from query Ov2

Maybe the way I created them? As you advised, these 3 queries are based on Qry_Calc, I grouped each of them by name and used "Sum" in the field "NoHours" for the 3 of them.

To what am I supposed to link the fourth query? Feels like I am missing something important...

 

[GinaWhipp]

You did put the names in the first three queries right?

 

[aucho22]

Yes I did!

 

[GinaWhipp]

Do all the names show up in any one of the queries, like the regular hours one?

 

[aucho22]

No, in all the queries, Firstname/Lastname are grouped. Each name appear only once with the sum of NoHours.

Example:

Steve Brown 2 regular hours
Janet Jones 3 regular hours
Steve Brown 2 regular hours

It would show up in query Reg_Hours as:
Steve Brown 4 regular hours
Janet Jones 3 regular hours

In each of the 3 queries I have the following fields:
Firstname
Lastname
SumofNoHours

I have attached a screenshot of the result of the fourth query when I use the 3 queries and Qry_Calc without any criteria...
The results go way down, displaying the other names, still duplicated with numbers...
I changed hours to minutes, in case it may change anything but visibly no...

 

[GinaWhipp]

I do not need the names to show in all the queries just one. So, you need to create the joins to the regular query that pull all the records from the RegHours query, understand?

 

[aucho22]

Right my apologies again but... What do you mean?

The only query where all the names show up is in Qry_Calc.
Then, the 3 other queries (needed for the report) only show either the regular hours (Rate 1), either overtime (Rate 1.5) either overtime (Rate 2).

Qry_Report (fourth query) is the only query to pull data from these 3 other queries, but I am not sure what you mean by "joins to the regular query that pull all the records from the RegHours query"?