Check out some similar questions!

ashley velez · Jun 18, 2009, 07:08 AM

I have an acess Database that my boss set up for me. I enter employee names what time they sign in sign out for lunch sign back in and sign out for the day... How do I make a report/or query that can give me a monthly average for each person?

ScottGem · Jun 18, 2009, 07:22 AM

To answer this I need to know the table structure. Are all the time entries in one record for each day?

The next step is to create a query that calculates the hours worked each day. From there it depends on what you mean by a monthly average. Do you mean a daily average for the month or a monthly average for a year or what?

The final step is to use the Report Wizard to create a report from the query. You can then Group by whatever period and Avg the totals.

ashley velez · Jun 18, 2009, 07:59 AM

I'm new to Access, I just really enter the times that is it...

Well, Under tables in the top left pane, there is one called sign_in_sheet, & It looks like a spread sheet with the info that I was telling you about. I'd like average weekly time, and average monthly time.

ScottGem · Jun 18, 2009, 08:05 AM

What it looks like is what I need to see. Can you do a screen shot and attach it to a post here or just list the columns. Even better would be to right click on the table name and select Design View and give me a screen shot of that as well. It would help to know the data types. It would also help to know what version of Access.

ashley velez · Jun 18, 2009, 08:12 AM

I attached it

ScottGem · Jun 18, 2009, 08:22 AM

Perfect! But you didn't say what version of Access. Also you are not making it clear, Do you want a DAILY avg by week and month? For example:

John works M-F during June. He works 40 hrs from 6/1-6/5. His Daily Average is 8 hrs. But he works 37 hrs the next week, 41 hrs the following week and 40 hrs the 4th week. His weekly average therefore is 39.5hrs. So you nreally need to define what you are looking for with more detail. Its fairly easy to do but depending on what you want requires slightly different formulas.

One other kicker to this. Does your workday span midnight. In other words, can a person's start time start on one day and the time out be the next day? Doesn't appear so from the names, but its an important fact to know.

ashley velez · Jun 18, 2009, 08:37 AM

I'd like all of those I guess, it would hurt to have a daily average, weekly average, and monthly average, I have Access 2007, from MS Office 2007

ashley velez · Jun 18, 2009, 08:55 AM

Scotty, come back, we were so close! Lol

ScottGem · Jun 18, 2009, 09:05 AM

First, please try to answer ALL my questions, otherwise you make it harder to help you. You didn't answer whether day can span midnight and you still aren't quite grasping what I'm saying about averages. A weekly average could mean one of two things; an averge per day for a week or an average per week for a greater length of time. I think what you really want is a daily average over various periods. So that's what I'll deal with.

1) Select the Create Ribbon and open Query Design mode.
2) Select the table and the employee table and add them to the query grid then close the tables list.
3) If a join line does not appear betweent he Employee name field in the time table and the employee ID field in the employee table then drag the name fields to the ID field to create a join.
4) Add the Date and the four time fields from the time table and the Employee Name from the employees table.
5) In the top row of the first blank column add the expression:
TimeWorked: DateDiff("n",[Time In AM],[Time Out Lunch]) + DateDiff("n",[Time In Lunch],[Time Out PM])
6) Save the quey as qryDailyTime. If you run the query and you should have the number of minutes for each day per person.
7) Use the Report wizard and create a report based on this query. When the wizard asks you for grouping levels chose Employee Name first, then double click on the Date field. This will add a second grouping for date by Month.
8) Click on the Grouping options button and change that to group by week. Then double click the date field again for a group by Month.
9) Click Next for sorting options. The first sort should be on Employee Name and the second on Date
10) Then press the Summary Options button and select the Avg of TimeWorked. You can also select the Summary Only checkbox, so you don't see the daily time.
11) now Finish up and view the report.
You should see a listing of weekly and Monthly averages of their daily times. The listing is going to be in minutes however. If you want to see the time in hours, open the report in Design mode and right click on the controls that display the AVG. You should see a ControlSource like:

=AVG([TimeWorked])
Change it to:
=AVG([TimeWorked])/60
to dsplay the hours and fraction of an hour.

P.S. Got to be patient. As you can see it took awhile to type out the instructions.

ashley velez · Jun 18, 2009, 09:09 AM

Sorry, no, people work usually between 5 am and 10 pm...

Weekly av, from Jan- current
Monthly av, Jan-current
Daily Av, Jan-current

Is what I meant by average.

I will try your method out, and maybe tweak it to what I need, who knows how it'll turn out, because I am not an Access Expert like yourself

ScottGem · Jun 18, 2009, 09:15 AM

I think my instructions will give enough to play with and maybe get the rest of the way.

I'll give you a hint, look into Group By queries and the DatePart function.

ashley velez · Jun 18, 2009, 09:34 AM

I am sorry, but I think I will need a little more help with this...

Also, I forgot to mention, not everyone goes to lunch, some employees just sign in and out once a day

ScottGem · Jun 18, 2009, 10:01 AM

Ahh, That certainly complicates things. Ok, lets try changing the expression in TimeWorked to:

TimeWorked: IIF(ISNull([Time Out Lunch]),DateDiff("n",[Time In AM],[Time Out PM]),DateDiff("n",[Time In AM],[Time Out Lunch]) + DateDiff("n",[Time In Lunch],[Time Out PM]))

That should give you the difference between time IN AM and Time Out PM if nothing was entered for Lunch.

ashley velez · Jun 18, 2009, 01:41 PM

Thank you very much for your help!.

ashley velez · Jun 19, 2009, 08:15 AM

Ok, I was able to create a query that shows how many hours were worked a week, with a monthly average... So I typically worked 40 hours a week, like that kind of report...

How would I go about, getting a sum of hours worked during a start and end date, where you can enter the employee, and just get records for that employee

ashley velez · Jun 19, 2009, 08:22 AM

...

ScottGem · Jun 19, 2009, 12:10 PM

First create a form, by going into Design View. This will be an unbound form (no recordsource). On the form, put two text boxes, call them txtStart and txtEnd. Then add a combobox to select the Employee (use the wizard). Name the combobox cboEmp.

Now create a query that on your table, including a column to calculate the daily time, like in the other query. In the Date column set the criteria to:
BETWEEN Forms!formname!txtStart AND Forms!formname!txtEnd
use the actual formname. For the employee column set the criteris to:
=Forms!formname!cboEmp.

Finally, add a button to your form to run your report.