Elapsed Time by Area

[sathishj1981]

New here,

I have a two tables

table A (Clockin)
Date EmpId StartTime Area
3/12/13 AA243 1100 001
3/12/13 AA253 1103 002
3/12/13 AA263 1108 003
3/12/13 AA273 1150 004

table B (Area Change and Clock out)
Date EmpId In Area
3/12/13 AA243 1530 003
3/12/13 AA253 1745 003
3/12/13 AA263 1617 002
3/12/13 AA273 1545 002
3/12/13 AA243 1630 003
3/12/13 AA253 1645 003
3/12/13 AA263 1617 002
3/12/13 AA273 1645 002

I can calculate total hours by employee (start time - clockout time (Max of Table b))
I need help calculating hours by Area, results like

Area Hours
001 4.30
002 12.6
003 9.7
004 3.95

Thanks for any help.

[June7]

Doesn't look easy.

What do you mean by 'Area Change'? Employee is moving between areas so the In time of one record is the out time of earlier In?

Not only have to combine two datasets with UNION query will then need subquery to calculate data between sequential records. Will really get complicated if the change occurs accross midnight. Date and time need to be one value for that.

Review http://allenbrowne.com/subquery-01.html especially the example for Get Value In Another Record. However, in your case can't rely on a single unique ID field. Requires the combination of Date, Time, EmpID to serve as a unique identifier and all three need to be sort criteria.

After all that, then do a Totals (aggregate) query grouping on the Area.

Your sample data in Table2 has two identical records for EmpID AA263.