Time Taken Calculation

[Guest]

I have two tables that I need to create a query to do some calculations
between.

Table 1 Stores:
Employee ID, Vacation Time Allotted, Personal Time Allotted, and Holiday
Time Allotted
Example: Employee 123 has 140 hours of vacation, 40 hours of personal time,
and 88 hours of holiday time.

Table 2 Stores:
Employee ID, TimeType (Field that represents what kind of time the employee
took: vacation, personal, holiday), date, and the amount of time taken on
that day.
Example: Employee 123 took 8 hours of personal time on 6/12.

I need to find a way to add up the amount of time taken in each category and
then subtract that from their overall allotted time.
Example: Employee 123 has taken personal time on three occasions: once for 8
hours and twice for four hours.
I need to add up all personal time taken and subtract it from the 40 hours
that the company has allotted them so they know how much they have left.

My problem with this lies in the fact that "TimeType" (Holiday, Personal,
and Vacation time) are all stored in one field, where each allottment of time
is in a separate field.
Any ideas?

 

[Guest]

Hi jbgarrett1,

I give below the steps you have to take to solve your problem and get the
result as desired.

I suggest you you to create a temporary database and do it as an hands on
session.

Step 1:

Create two tables with fields as under:

Table1
Field name Datatype
EmpID Number
VacationTimeAllotted Number
PersonalTimeAllotted Number
HolidayTimeAllotted Number

Table2
EmpID Number
TimeType Text
Date Date
TimeTaken Number

For TimeType, select Look Up Wizard and then under "I will type the values
in the list" type "Holiday", "Vacation" and "Personnel".


Step 2:
In Table1 and Table2 fill the data as under.

Table1
EmpID VacationTimeAllotted PersonalTimeAllotted HolidayTimeAllotted
123 140 40 88
150 120 50 90

Table2
EmpID TimeType Date TimeTaken
123 Personal 6/1/2006 8
123 Personal 6/6/2006 4
123 Personal 6/12/2006 4
123 Vacation 5/12/2006 40
123 Vacation 4/12/2006 20
123 Holiday 4/4/2006 8
123 Holiday 6/1/2006 4
150 Personal 6/3/2006 10
150 Personal 6/9/2006 7
150 Personal 6/11/2006 3
150 Vacation 5/20/2006 32
150 Vacation 3/14/2006 26
150 Holiday 5/7/2006 8
150 Holiday 6/10/2006 6

Step 3:
Create the following queries. I have given the SQL which you can paste in
SQL view by creating a new query and save as appropriate.

qryTotalTime
SELECT Table2.EmpID, Table2.TimeType, Sum(Table2.TimeTaken) AS SumOfTimeTaken
FROM Table2
GROUP BY Table2.EmpID, Table2.TimeType;

qryVacationBalance
SELECT Table1.EmpID, Table1.VacationTimeAllotted, qryTotalTime.TimeType,
qryTotalTime.SumOfTimeTaken, [VacationTimeAllotted]-[SumOfTimeTaken] AS
Balance
FROM Table1 INNER JOIN qryTotalTime ON Table1.EmpID = qryTotalTime.EmpID
WHERE (((qryTotalTime.TimeType)="Vacation"));

qryPersonalBalance
SELECT Table1.EmpID, Table1.PersonalTimeAllotted, qryTotalTime.TimeType,
qryTotalTime.SumOfTimeTaken, [PersonalTimeAllotted]-[SumOfTimeTaken] AS
Balance
FROM Table1 INNER JOIN qryTotalTime ON Table1.EmpID = qryTotalTime.EmpID
WHERE (((qryTotalTime.TimeType)="Personal"));

qryHolidayBalance
SELECT Table1.EmpID, Table1.HolidayTimeAllotted, qryTotalTime.TimeType,
qryTotalTime.SumOfTimeTaken, [HolidayTimeAllotted]-[SumOfTimeTaken] AS Balance
FROM Table1 INNER JOIN qryTotalTime ON Table1.EmpID = qryTotalTime.EmpID
WHERE (((qryTotalTime.TimeType)="Holiday"));

(Check all queries in datasheet view.If you feel o.k. as per sample data
then proceed.)

Now you have to create a union query as under. This is also similar to
normal query except that it can be created only in SQL view in the Query
design grid. I have already created the union query as given below. Just copy
and paste it and save it as per the name given.

qryUnionBalanceViewAll

SELECT Table1.EmpID, Table1.VacationTimeAllotted as TimeAllotted,
qryTotalTime.TimeType, qryTotalTime.SumOfTimeTaken as TimeTaken,
[VacationTimeAllotted]-[SumOfTimeTaken] AS Balance
FROM Table1 INNER JOIN qryTotalTime ON Table1.EmpID = qryTotalTime.EmpID
WHERE (((qryTotalTime.TimeType)="Vacation"));

UNION ALL SELECT Table1.EmpID, Table1.PersonalTimeAllotted as TimeAllotted,
qryTotalTime.TimeType, qryTotalTime.SumOfTimeTaken as TimeTaken,
[PersonalTimeAllotted]-[SumOfTimeTaken] AS Balance
FROM Table1 INNER JOIN qryTotalTime ON Table1.EmpID = qryTotalTime.EmpID
WHERE (((qryTotalTime.TimeType)="Personal"));

UNION ALL SELECT Table1.EmpID, Table1.HolidayTimeAllotted as TimeAllotted,
qryTotalTime.TimeType, qryTotalTime.SumOfTimeTaken as TimeTaken,
[HolidayTimeAllotted]-[SumOfTimeTaken] AS Balance
FROM Table1 INNER JOIN qryTotalTime ON Table1.EmpID = qryTotalTime.EmpID
WHERE (((qryTotalTime.TimeType)="Holiday"))
ORDER BY Table1.EmpID, qryTotalTime.TimeType;

Check the union query in datasheet view.

If you see the result as given below then you have got what you wanted.

EmpID TimeAllotted TimeType TimeTaken Balance
123 88 Holiday 12 76
123 40 Personal 16 24
123 140 Vacation 60 80
150 90 Holiday 14 76
150 50 Personal 20 30
150 120 Vacation 58 62

Now you can create a form based on this union query and can filter records
on EmpID, TimeType, etc using look up Combo boxes or whatever you like.

Good Luck,
Surendran