counting days worked per month

[Stuff3]

I have a table giving start date and end dates for each person working on a
variety of projects. I need to be able to calculate for each person how many
days they have worked during a given calender month and then total all the
days for all people that month. It is not necessary to exclude weekends or
holidays but i need to be able to count the days worked each month for
projects that might start mid January and finish mid March.
I am new to this so please bear with me and assume limited knowledge. Thanks

 

[Klatuu]

How do you know which days a person worked?

 

[Stuff3]

The client is charged for all days our people are away so it includes
weekends etc -Each project is a chunk of time from a few days to a few months
without any breaks. So we only need to know when they left and when they
returned - all days in between are chargeable.

 

[vanderghast]

*if* you have a table, limits, one field, limit, with the first day of each
month you are interested:

limits 'table name
limit 'field
2009.01.01
2009.02.01
2009.03.01
2009.04.01
2009.05.01
2009.06.01 ' data sample



*if* your working dates data is like


works ' table name
empID, fromDate, toDate ' fields
1010 2009.01.17 2009.03.15
1010 2009.04.12 2009.04.22 ' data sample


then


SELECT empID,
limit AS firstOfMonth,
DateSerial(Year(limit), 1+Month(limit), 0) AS lastOfMonth
iif(works.fromDate>limits.limit, works.fromDate, limist.limit) As
starting,
iif(works.toDate>lastOfMonth, lastOfMonth, works.toDate) As ending,
1+ ending - starting As numberOfDays
FROM works INNER JOIN limits
ON limits.limit >= DateSerial(Year(works.fromDate),
Month(works.fromDate), 1)
AND limits.limit <= DateSerial(Year(works.toDate), Month(works.toDate),
1)


produces, by month, by empID, the number of days the employee has worked:

-------------------------------
empID firstOfMonth lastOfMonth starting ending
numberOfDays
1010 2009.01.01 2009.01.31 2009.01.17 2009.01.31 15
1010 2009.02.01 2009.02.28 2009.02.01 2009.02.28 28
1010 2009.03.01 2009.03.31 2009.03.01 2009.03.15 15
1010 2009.04.01 2009.04.30 2009.04.12 2009.04.22 11

(here, the employee ID has worked 15 days in jan 2009, 28 in feb 2009,
etc...)
--------------------------------


You can make another query based on this one, to make your SUM, GROUP by
firstOfMonth, to get the over all empID totals.



Vandeghast, Access MVP

 

[Klatuu]

Look in VBA Help for the DateDiff function. It will give you all the detail,
but basically, you can use a calculated field in your query to return the
number of days between two dates. Note that you will need to add 1 to the
result. That is because if you call DateDiff using yesterday's and today's
dates, it will return 1; but, you will want to include both days in the
count. Here is an example of how it can be done in your query:

ChargeDays: DateDiff("d", [DateLeft], [DateReturned])

 

[John Spencer MVP]

If you are doing a specific month then this is fairly simple, you will use the
DateDiff function and the StartDate or the beginning of the month(BOM) and the
EndDate or the end of the month(EOM).

Generically, the query to get the total number of days would look something
like the following.

Parameters BOM DateTime, EOM DateTime;
SELECT ProjectID,
SUM(1 +
DateDiff("d",IIF(StartDate<[BOM],[BOM],StartDate),IIF(EndDate>[EOM],[EOM],EndDate)))
as ChargeDays
FROM SomeTable
WHERE StartDate <= [EOM] and EndDate >= [BOM]
GROUP BY ProjectID

You would be prompted for the EOM (end of the period - end of month) and BOM
(beginning of the period - first of the month)

Building this in query design view:
-- Add the table
-- add the fields for ProjectID, StartDate and EndDate, add the field
StartDate a second time
-- add the paramaters BOM and EOM by selecting Query: Parameters from the menu
and entering BOM in column 1 and DateTime is column 2 of the dialog. repeat
for EOM
-- Under startdate enter criteria of <=EOM
-- Under enddate enter criteria of >=BOM
-- Select View: Totals
-- Change GROUP BY to WHERE under StartDate and EndDate
-- Change Group by to SUM under the second startdate field
-- EDIT the second StartDate to read like the following using your field names
ChargeDays: 1 +
DateDiff("d",IIF([StartDate]<[BOM],[BOM],[StartDate]),IIF([EndDate]>[EOM],[EOM],[EndDate]))

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County