Calculated total scheduled work hrs per day and per week in Access

[Guest]

I feel like I have tried everything, and I KNOW this is a simple task, for
those who are very familiar with Access, but I am having the biggest problems.

I have constructed a database to keep track of employees and also use the
dbase as a scheduling tool. Basically, an employee's hours will be entered
by choosing a week-ending date (this was the best way I could think of), then
using 30-min. interval drop-down boxes to select the start and end times of
their shift. The list box hails from a table I have made, formatted as
"date/time", "short time".

I also need to be able to calculate the hours scheduled for a shift (ex.
7am to 3pm = hrs) and also total their weekly hours. I want this to
autocalculate when the schedule data are being entered into a form. From
what I gather, a query with hour totals for each day and a combined total #
hrs would work best.

BUT, I am having a horrible time constructing the queries - always receiving
error messages, and then when I don't receive an error message, the
calculations do not happen when I enter, say, 7am sunday start to 3 pm sunday
end. I am not entirely familiar w/ Macros, VBA, or SQL, so if you provide
comments concerning those, please explain just a bit so I may understand.

Please help, I am sure this is elementary to most who are familiar with
access, but I am at the point of ripping my hair out over this, just kidding,
but it is stressful. Help would be greatly appreciated!

 

[Michel Walsh]

Hi,


You can get the interval of time by simple subtraction of two date_time
field, note that the answer is in days and decimal of days ( 0.25 = 6 hours,
one quarter of a day).



SELECT employeeID,
datePart("ww", ending) As weekNumber,
SUM(ending-starting) * 24 As numberOfHours

FROM myTable

GROUP BY employeeID,
datePart("ww", ending)



could thus be a possible solution.


Hoping it may help,
Vanderghast, Access MVP