-----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.StaffID
WorkDate DateTime
SlotID Long integer FK to TimeSlots.SlotID
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:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/AwUBQDWp84echKqOuFEgEQIzGwCeIDLUfOrc93XhKNFIHO9Jpe ZwCNoAn29I
jGQwiogH5VMmaoQFy0p6DW5O
=QkRa
-----END PGP SIGNATURE-----
sg*****@yahoo.com.sg wrote:
================================================== ==========================
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.xls). 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?
