Staffs monthly time sheet query calculation

[cobby]

Please help me do the following calculation for each month time sheet. In my database query, I have the following fields, StaffID, StartTime, EndTime and TotalHours. TotalHours is calculated by finding the time difference between EndTime and StartTime. Now, I want to be able to calculate the sum of TotalHours for each employee every month, either by the user specifying monthly dates say; December or (1/12/2009 to 31/12/2009), January or (1/01/2009 to 31/01/2009), February or (1/02/2009 to 28/12/2009)........ I really appreciate your help.
[FONT=&quot]Regards,[/FONT]

 

[wilpeter]

I'm making some assumptions here: A person works both morning and afternoon and clocks in and out daily at random times? Wages rather than salary, so not paid for lunch hour? Possibly 20-50 entries per person in the same month? If so, the Query will calculate hours for each record
with a formula like TotalHours: ((EndTime)-(StartTime))*24
Using that Query as the source of the second query, turning on Totals, with fields StaffID (GroupBy); DateWorked (Where : Between [Enter Start Date] And [Enter End Date] ); and TotalHours (Sum)

However, why not use a Report from the Query instead and Group it by Month (based on the first query)? Just print the Summary totals.

 

[cobby]

Thanks Wilpeter for your reply. Your suggestion seems very good, just that if I create buttons to calculate a query report for January, February and so on for the year 2010, what about the year 2011 or 2012.... Ideally, I will want a window to popup so users can put in the dates say 01/01/2010 to 31/01/2010 to return TotalTime worked for January, same for February and so on. I am struggling how to go about this. Please help. I have already calculated the time difference and group by StaffID.

 

[wilpeter]

Did you actually try the second query method? By using as criteria for the DateWorked field (assuming you have one--you didn't mention it), in square brackets, you are forcing a pop-up for Start and End of the period you require.

 

[DCrake]

Here is a form I have used in the past that attempts to guesstimate the typical date ranges that the user may want to runt the report for. Of course this can be expanded to suit your individual needs, but should give you a pointer to making it easier for the user.



David

 

[wilpeter]

OK, instead of using a parameter that is prompted for by the 2nd Query, you tell the Query where to find the two dates by including the path. The criteria would become something like:
Between Forms![Report Generator]![Start Date] And Forms![Report Generator]![End Date]
depending what Name property was assigned to the two fields. The query will only run while the Report Generator remains Open, so I'm assuming the requestor uses the Finish button to run the two Queries by means of a macro.

 

[raskew]

Hi -

Provided you're looking for complete months, this statement placed in the criteria cell of your query's date field will return a Between statement listing
the start and end dates of the month. Note that it does involve one parameter
pop-up but there's no need to compute the start and end dates.

Between DateValue([Enter mm/yyyy]) And DateAdd("m",1,DateValue([Enter mm/yyyy]))-1

HTH - Bob

 

[cobby]

thanks very much Bob. I will have a go at it now.

 

[cobby]

[FONT=&quot]Hi Bob, thanks a million for your help, it has worked well but there is another problem which I think requires if and else statement. When you research by putting in the month say 12/2009 its returns the number of records with negative amount in the column like (-4) which I think means 4 records according to records found in the table. Why is it showing negative and not positive? Also, I would want the query to return “records not found” if dates equals 0 and not to show any record and a search to show records according to dates search only. Thanks in advance for your help. [/FONT]

 

[raskew]

Hi -

Please post the query-SQL for your report. What I provided
was intended only as an easy way to create date parameters
by entering only mm/yyyy, e.g. 12/2009.

Bob