>Dear All,
>
>I want to generate a table from a source table like
>
>
>staff_id
>record_time
>record_type: i.e. 1=Day In;2=Lunch start; 3=Lunch End;4=Day Out
>
>
>Therefore, basically, if the staff comes for whole day, he/she could have 4 records in the source table e.g.
>
>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
>
>
>To generate a view for "timecard", I want to have a cursor table like this:
>
>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
>
>
>PLEASE HELP
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)
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.