how to calculate man hours for different project ?

**MGFoster** · Nov 12 '05, 08:36 PM

Re: how to calculate man hours for different project ?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

First, put the data into normalized tables so you can create queries
without tearing your hair out in frustration. Copy Excel data into a
temporary table before starting.

Let's make some lookup tables before the main table:

StaffNames
StaffID Long integer - your Staff Number - primary key
StaffName Text

=============== ====== 2 definitions of TimeSlots

TimeSlots (1)
SlotID AutoNumber (long integer)
StartTime DateTime - only store the Time
EndTime DateTime - only store the Time

Primary Key is StartTime & End Time. SlotID is a unique index.

You could also design the table like this:

TimeSlots (2)
SlotID AutoNumber
Duration Byte (0-255 hrs)

Primary Key is Duration. SlotID is a unique index.

This TimeSlots design (2) would be used if you just wanted the hours
worked for the time slot and weren't interested in the actual time
begin & end per time slot. This table design makes the query easier
to write 'cuz it won't have to calculate the time between Start & End
time.

=============== ======

Now the table that holds the staffs' project times (main table):

ProjectTimes
StaffID Long integer FK to StaffNames.Staf fID
WorkDate DateTime
SlotID Long integer FK to TimeSlots.SlotI D
ProjectCode Text(1)

Primary Key of all fields, or all fields except ProjectCode, if the
staff member can only work on one project at a time. To be safe make
PK all fields.

[Queries using JET SQL]

Query to get hours worked - using TimeSlots (2) table:

SELECT PT.ProjectCode, Sum(TS.Duration ) As HrsWorked
FROM ProjectTimes As PT INNER JOIN TimeSlots As TS
ON PT.SlotID = TS.SlotID
WHERE PT.ProjectCode In ("A", "B", "C", "D")

Using TimeSlots (1) table:

SELECT PT.ProjectCode, Sum(TS.Duration ) As HrsWorked
FROM ProjectTimes As PT INNER JOIN
[ SELECT SlotID, DateDiff("h", StartTime, EndTime) As Duration
FROM TimeSlots ]. As TS ON PT.SlotID = TS.SlotID
WHERE PT.ProjectCode In ("A", "B", "C", "D")

This query uses an undocumented feature of JET SQL - the []'s around a
tabular subquery. Be sure to put a period immediately after the right
hand bracket.

Easier maintenance is using TimeSlots (1) table 'cuz all you have to
do is change the Start/End times & the calculation query will get the
correct duration. TimeSlots (2) table requires db maintainer to
correctly calc the duration & update the Duration column - human
arithmetic error possible.

The hard part is putting the Excel data into the normalized tables.

TimeSlots can be entered by hand (only 4 records).

StaffNames table can be loaded by reading the Staff Number & Staff
Name columns:

INSERT INTO StaffNames ...
SELECT DISTINCTROW StaffNumber, StaffName
FROM <temp table>)

DISTINCTROW (hopefully) eliminates duplicates.

ProjectTimes will have to be filled by reading Staff Number and
ProjectCode for each Time column. You'll have to manually put in the
SlotID & WorkDate for each INSERT INTO query. You'll have 1 INSERT
query for each column of time slots. Just set up 1 INSERT query w/
the criteria for the 1st column in the temp table & run it. Then
change the criteria to the next column & run query. Continue until
all columns have been moved into the ProjectTimes table.

Should only take about 1 hr to set up tables & move data into tables.

MGFoster:::mgf0 0 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQDWp84echKq OuFEgEQIzGwCeID LUfOrc93XhKNFIH O9JpeZwCNoAn29I
jGQwiogH5VMmaoQ Fy0p6DW5O
=QkRa
-----END PGP SIGNATURE-----

sg_s123@yahoo.c om.sg wrote:
[color=blue]
> =============== =============== =============== =============== =============== =
> 02-Feb-04 03-Feb-04
> Staff Staff 0800hr- 1300hr- 1700hr- 1900hr- 0800hr- 1300hr- 1700hr-
> 1900hr-
> Number Name 1200hr 1700hr 1900hr 2200hr 1200hr 1700hr 1900hr
> 2200hr
> =============== =============== =============== =============== =============== =
> 4654 Saniff A A A C A A A C
> 4437 Joey B C B C B C B C
> 4479 Elmer C C B C C C B C
> 4484 Garry C C A B C C A B
> 4509 Philip A C A B A C A B
> 4903 Siti A C A B A C A B
>
>
> => The above monthly data is from excel (staffproject.x ls). It comes
> with staff number, staff name and 1 month timeslot data (divided into
> 4 time slots for each day). Each time slot is equivalent to the 4
> hours, 4 hours, 2 hours, 3 hours respectively. "A,B,C,D" represent
> project code. My question is :
>
> 1. I have import te excel xls into access database. Using Access, How
> do I calculate for Project A,B,C,d, how many man hours are used for
> different project?[/color]

how to calculate man hours for different project ?

how to calculate man hours for different project ?

Comment