Need help with time in a row.

[Victor Torres]

Hi to all,

I need some help into creating an interface between a already made program
and my payroll program. The other program database is made in MS Access and
I can have access to it. My problem is that their transaction list is in
rows instead of columns. Let me explain:

TimeCardID DateTimeTrans

10 03/04/2008 8:00am
10 03/04/2008 12:00pm
10 03/04/2008 1:00pm
10 03/04/2008 5:00pm
11 03/04/2008 8:00am
11 03/04/2008 12:00pm
11 03/04/2008 1:00pm
11 03/04/2008 5:00pm

What I want is this:

TimeCardID Date Time Difference
10 03/04/2008 8:00am 0
10 03/04/2008 12:00pm 4
10 03/04/2008 1:00pm 1
10 03/04/2008 5:00pm 4
11 03/04/2008 8:00am 0
11 03/04/2008 12:00pm 4
11 03/04/2008 1:00pm 1
11 03/04/2008 5:00pm 4

or better:

TimeCardID Date In Outlunch Inlunch Out

10 03/04/2008 8:00am 12:00pm 12:00pm 12:00pm
11 03/04/2008 8:00am 12:00pm 12:00pm 12:00pm

Is this posible??? please this is very important to me.

 

[KARL DEWEY]

Try this ---
TRANSFORM First([DateTimeTrans]-Int([DateTimeTrans])) AS Expr1
SELECT Victor.TimeCardID, Format([DateTimeTrans],"Short Date") AS [Trans Date]
FROM Victor
GROUP BY Victor.TimeCardID, Format([DateTimeTrans],"Short Date")
PIVOT
IIf([DateTimeTrans]-Int([DateTimeTrans])<0.4,"In",IIf([DateTimeTrans]-Int([DateTimeTrans])<0.5,"Out
Lunch",IIf([DateTimeTrans]-Int([DateTimeTrans])<0.6,"In Lunch","Out"))) In
("In", "Out Lunch", "In Lunch", "Out");

 

[Michel Walsh]

To get the first result, try:


SELECT a.timeCardID, a.dateTime, Nz((MAX(b.dateTime)-a.dateTime)*24.0, 0)
As DifferenceInHour
FROM myTableName AS a LEFT JOIN myTableName AS b
ON a.timeCardID=b.timeCardID
AND a.dateTime > b.dateTime
GROUP BY a.timeCardID, a.dateTime



You can also 'compute' an expression to know if the involved time is an IN
or an OUT:



SELECT a.timeCardID,
a.dateTime,
iif( 0=COUNT(b.timeCardID) MOD 2, "OUT", "IN") AS inOrOut,
Nz((MAX(b.dateTime)-a.dateTime)*24.0, 0) As DifferenceInHour

FROM myTableName AS a LEFT JOIN myTableName AS b
ON a.timeCardID=b.timeCardID
AND a.dateTime > b.dateTime

GROUP BY a.timeCardID, a.dateTime




Hoping it may help,
Vanderghast, Access MVP

 

[Victor Torres]

Hi Karl,

Your example is the most accurate to what I need. But it still have some
problems. It gives me this:

TimeCardID Trans Date In Out Lunch In Lunch Out
20 2/15/2008 8:00:00 AM 12:02:00 PM 4:17:00 PM
20 2/18/2008 8:00:00 AM 12:30:00 PM 5:28:00 PM
20 2/19/2008 9:00:00 AM 12:20:00 PM 7:50:00 PM

It doesn't give me any data to the in lunch and instead it put the out lunch
in the in lunch. What could be wrong???Thanks a lot for all your help!!

Try this ---
TRANSFORM First([DateTimeTrans]-Int([DateTimeTrans])) AS Expr1
SELECT Victor.TimeCardID, Format([DateTimeTrans],"Short Date") AS [Trans Date]
FROM Victor
GROUP BY Victor.TimeCardID, Format([DateTimeTrans],"Short Date")
PIVOT
IIf([DateTimeTrans]-Int([DateTimeTrans])<0.4,"In",IIf([DateTimeTrans]-Int([DateTimeTrans])<0.5,"Out
Lunch",IIf([DateTimeTrans]-Int([DateTimeTrans])<0.6,"In Lunch","Out"))) In
("In", "Out Lunch", "In Lunch", "Out");

--
KARL DEWEY
Build a little - Test a little

Hi to all,

I need some help into creating an interface between a already made program
and my payroll program. The other program database is made in MS Access and
I can have access to it. My problem is that their transaction list is in
rows instead of columns. Let me explain:

TimeCardID DateTimeTrans

10 03/04/2008 8:00am
10 03/04/2008 12:00pm
10 03/04/2008 1:00pm
10 03/04/2008 5:00pm
11 03/04/2008 8:00am
11 03/04/2008 12:00pm
11 03/04/2008 1:00pm
11 03/04/2008 5:00pm

What I want is this:

TimeCardID Date Time Difference
10 03/04/2008 8:00am 0
10 03/04/2008 12:00pm 4
10 03/04/2008 1:00pm 1
10 03/04/2008 5:00pm 4
11 03/04/2008 8:00am 0
11 03/04/2008 12:00pm 4
11 03/04/2008 1:00pm 1
11 03/04/2008 5:00pm 4

or better:

TimeCardID Date In Outlunch Inlunch Out

10 03/04/2008 8:00am 12:00pm 12:00pm 12:00pm
11 03/04/2008 8:00am 12:00pm 12:00pm 12:00pm

Is this posible??? please this is very important to me.