Daily Clock Rings

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an import table tha has employee hourly clock rings. My problem is
that an employee may clock in late one night on one date but continue to the
next day with another Date. How can I make an application that will change:


Test1
Name Day Operation Time
RICHIE CG 8-Jul-06 BT 4
RICHIE CG 8-Jul-06 ET 10.25
RICHIE CG 10-Jul-06 BT 22.25
RICHIE CG 11-Jul-06 OL 0.5
RICHIE CG 11-Jul-06 IL 1.5
RICHIE CG 11-Jul-06 ET 7
RICHIE CG 12-Jul-06 BT 3
RICHIE CG 12-Jul-06 OL 10.16
RICHIE CG 12-Jul-06 IL 10.86
RICHIE CG 12-Jul-06 ET 13.62
RICHIE CG 13-Jul-06 BT 20
RICHIE CG 13-Jul-06 OL 22.5
RICHIE CG 14-Jul-06 IL 0.5
RICHIE CG 14-Jul-06 ET 6


To look like:

Test1 Results
Name Day BT OL IL ET
RICHIE CG 08-Jul-06 4 10.25
RICHIE CG 10-Jul-06 22.25 0.5 1.5 7
RICHIE CG 12-Jul-06 3 10.16 10.86 13.62
RICHIE CG 13-Jul-06 20 22.5 0.5 6

I appreciate any help from anyone out there.

TY
 
b5917bts said:
Thank You very much for your assistance. I followed Ypur instructions and
this is what I got:

Name Day BT OL IL ET
RICHIE CG 08-Jul-06 4 10.25
RICHIE CG 10-Jul-06 22.25
RICHIE CG 11-Jul-06 0.5 1.5 7
RICHIE CG 12-Jul-06 3 10.16 10.86 13.62
RICHIE CG 13-Jul-06 20 22.5
RICHIE CG 14-Jul-06 0.5 6


This is close to what I want but I have a problem calculating total hours.
This is because as one example above, the employee began on 10-Jul-06 at
22.25 but went out to lunch at .50 hundreds (12:30am)on 11-Jul-06. He also
came back from lunch and ended his tour on 11-Jul-06 also. His OL, il and ET
are in a different record than his BT. I am trying to have his BT, OL, IL and
ET on the same record. Is there any possible way to do this.

TY
Click to expand...
 
You need to use a CrossTab query

Something like

TRANSFORM Last(TableName.Time) AS LasOfTime
SELECT TableName.Name, TableName.Day
FROM TableName
GROUP BY TableName.Name, TableName.Day
PIVOT TableName.Operation
 
Thank You very much for your assistance. I followed Ypur instructions and
this is what I got:

Name Day BT OL IL ET
RICHIE CG 08-Jul-06 4 10.25
RICHIE CG 10-Jul-06 22.25
RICHIE CG 11-Jul-06 0.5 1.5 7
RICHIE CG 12-Jul-06 3 10.16 10.86 13.62
RICHIE CG 13-Jul-06 20 22.5
RICHIE CG 14-Jul-06 0.5 6


This is close to what I want but I have a problem calculating total hours.
This is because as one example above, the employee began on 10-Jul-06 at
22.25 but went out to lunch at .50 hundreds (12:30am)on 11-Jul-06. He also
came back from lunch and ended his tour on 11-Jul-06 also. His OL, il and ET
are in a different record than his BT. I am trying to have his BT, OL, IL and
ET on the same record. Is there any possible way to do this.

TY
 
You need to use a Cross Tab Query.

Something like

TRANSFORM Last(TableName.Time) AS LastמתוךTime
SELECT TableName.Name, TableName.Day
FROM TableName
GROUP BY TableName.Name, TableName.Day
PIVOT TableName.Operation
 
Back
Top