>staff_id >record_time >record_type: i.e. 1=Day In;2=Lunch start; 3=Lunch End;4=Day Out >>
>staff_id Record_time Record_type >-------------------------------------- >1 2006-09-01 09:00:00 1 >1 2006-09-01 13:00:00 2 >1 2006-09-01 14:30:00 3 >1 2006-09-01 18:00:00 4 >>
>staff_id record_date day_in lunch start Lunch end Day_out >---------------------------------------------------------------------- >1 2006-09-01 09:00:00 13:00:00 14:30:00 18:00:00 >>
SELECT Staff_Id,; TTOD(Record_time) AS Record_Date,; MAX(IIF(Record_type=1,TTOC(Record_Time,2),SPACE(8))) AS Day_In,; MAX(IIF(Record_type=2,TTOC(Record_Time,2),SPACE(8))) AS lunch_start,; MAX(IIF(Record_type=3,TTOC(Record_Time,2),SPACE(8))) AS lunch_end,; MAX(IIF(Record_type=4,TTOC(Record_Time,2),SPACE(8))) AS Day_Out; FROM MyTable; GROUP BY Staff_Id, 2; INTO CURSOR crsTest BROSEE NORMAL(not tested)