Next record in current row

[krle]

Hi,

I have table name "tblTimeLog" with following field names:

TRID
DEPT
CARDNO
NAME
LOGDATE
LOGTIME
ACTION

Data in table show this way:

TRID DEPT CARDNO NAME LOGDATE LOGTIME ACTION
1 Admin 100 Tom 07.05.2009 8:40:12 AM 1
2 Admin 100 Tom 07.05.2009 5:05:30 PM 2
3 Sales 200 Helen 08.05.2009 8:46:29 AM 1
4 Sales 200 Helen 08.05.2009 1:56:58 PM 2
5 Sales 300 John 04.05.2009 9:33:54 AM 1
6 Sales 300 John 04.05.2009 8:38:10 PM 2
7 CA 400 Julia 04.05.2009 10:57:53 PM 2
8 CC 500 Peter 05.05.2009 3:55:04 PM 1
9 CC 500 Peter 05.05.2009 10:01:42 PM 1

Where action number 1 is Entrance and 2 is Exit.

I need to create new table that will have look this way:

DEPT CARDNO NAME LOGDATE IN OUT
Admin 100 Tom 07.05.2009 8:40:12 AM 5:05:30 PM
Sales 200 Helen 08.05.2009 8:46:29 AM 1:56:58 PM
Sales 300 John 04.05.2009 9:33:54 AM 8:38:10 PM
CA 400 Julia 04.05.2009 10:57:53 PM
CC 500 Peter 05.05.2009 3:55:04 PM
CC 500 Peter 05.05.2009 10:01:42 PM

Empty fields are ok if there is no data according action.

Please help.

 

[KARL DEWEY]

Try this --
SELECT tblTimeLog.DEPT, tblTimeLog.CARDNO, tblTimeLog.NAME,
tblTimeLog.LOGDATE,
Max(IIf([tblTimeLog].[ACTION]="1",[tblTimeLog].[LOGTIME])) AS [IN],
Max(IIf([tblTimeLog_1].[ACTION]="2",[tblTimeLog_1].[LOGTIME])) AS OUT
FROM tblTimeLog LEFT JOIN tblTimeLog AS tblTimeLog_1 ON (tblTimeLog.NAME =
tblTimeLog_1.NAME) AND (tblTimeLog.CARDNO = tblTimeLog_1.CARDNO) AND
(tblTimeLog.DEPT = tblTimeLog_1.DEPT)
GROUP BY tblTimeLog.DEPT, tblTimeLog.CARDNO, tblTimeLog.NAME,
tblTimeLog.LOGDATE;

 

[Marshall Barton]

I have table name "tblTimeLog" with following field names:

TRID
DEPT
CARDNO
NAME
LOGDATE
LOGTIME
ACTION

Data in table show this way:

TRID DEPT CARDNO NAME LOGDATE LOGTIME ACTION
1 Admin 100 Tom 07.05.2009 8:40:12 AM 1
2 Admin 100 Tom 07.05.2009 5:05:30 PM 2
3 Sales 200 Helen 08.05.2009 8:46:29 AM 1
4 Sales 200 Helen 08.05.2009 1:56:58 PM 2
5 Sales 300 John 04.05.2009 9:33:54 AM 1
6 Sales 300 John 04.05.2009 8:38:10 PM 2
7 CA 400 Julia 04.05.2009 10:57:53 PM 2
8 CC 500 Peter 05.05.2009 3:55:04 PM 1
9 CC 500 Peter 05.05.2009 10:01:42 PM 1

Where action number 1 is Entrance and 2 is Exit.

I need to create new table that will have look this way:

DEPT CARDNO NAME LOGDATE IN OUT
Admin 100 Tom 07.05.2009 8:40:12 AM 5:05:30 PM
Sales 200 Helen 08.05.2009 8:46:29 AM 1:56:58 PM
Sales 300 John 04.05.2009 9:33:54 AM 8:38:10 PM
CA 400 Julia 04.05.2009 10:57:53 PM
CC 500 Peter 05.05.2009 3:55:04 PM
CC 500 Peter 05.05.2009 10:01:42 PM

Empty fields are ok if there is no data according action.

SELECT R.Dept, R.CardNo. R.[Name], R.LogDate,
R.LogTime As InTime, S.LogTime As OutTime
FROM tblTimeLog As R Left Join tblTimeLog As S
ON R.CardNo = S.CardNo
UNION ALL
SELECT S.Dept, S.CardNo. S.[Name], S.LogDate,
Null, S.LogTime
FROM tblTimeLog As S Left Join tblTimeLog As R
ON S.CardNo = R.CardNo
WHERE R.CardNo Is Null

You did not explain why you want to create a new table. It
is usually sufficient to just use a query like the above
instead of another table, but if it really is necessary just
change use the query menu to change it to a make table
query.

 

[krle]

Thanks for reply.

This work fine but only with one IN and one OUT record for each employee.
Problem is when I have more logs for one person per day.

TRID DEPT CARDNO NAME LOGDATE LOGTIME ACTION
1 Admin 100 Tom 07.05.2009 8:40:12 AM 1
2 Admin 100 Tom 07.05.2009 11:05:30 AM 2
3 Admin 100 Tom 07.05.2009 1:46:29 PM 1
4 Admin 100 Tom 07.05.2009 2:50:30 PM 2
5 Admin 100 Tom 07.05.2009 3:30:10 PM 2
6 Admin 100 Tom 07.05.2009 5:10:55 PM 1

In this case query don't show all data for each employee.

The result I need:
DEPT CARDNO NAME LOGDATE IN OUT
Admin 100 Tom 07.05.2009 8:40:12 AM 11:05:30 AM
Admin 100 Tom 07.05.2009 1:46:29 PM 2:46:29 PM
Admin 100 Tom 07.05.2009 3:30:10 PM

Admin 100 Tom 07.05.2009 5:10:55 PM

Any help will be appreciated.

Thanks

Try this --
SELECT tblTimeLog.DEPT, tblTimeLog.CARDNO, tblTimeLog.NAME,
tblTimeLog.LOGDATE,
Max(IIf([tblTimeLog].[ACTION]="1",[tblTimeLog].[LOGTIME])) AS [IN],
Max(IIf([tblTimeLog_1].[ACTION]="2",[tblTimeLog_1].[LOGTIME])) AS OUT
FROM tblTimeLog LEFT JOIN tblTimeLog AS tblTimeLog_1 ON (tblTimeLog.NAME =
tblTimeLog_1.NAME) AND (tblTimeLog.CARDNO = tblTimeLog_1.CARDNO) AND
(tblTimeLog.DEPT = tblTimeLog_1.DEPT)
GROUP BY tblTimeLog.DEPT, tblTimeLog.CARDNO, tblTimeLog.NAME,
tblTimeLog.LOGDATE;

Hi,

[quoted text clipped - 36 lines]

Please help.