To display IN time and OUT time in seperate fields

[hygeena]

Hi,

I m working with access.we hav a table history stores the employee IN/OUT time details...The IN/OUT time details s stored in the same field as event time....I want to get the IN time and OUT time in seperate columns..
like below


IN OUT
9:00AM 6:00 PM

the 9:00AM nd 6:00 PM s stored in the same field as event....

can u help me?

Query

SELECT DISTINCT History.UserID, History.UserName, History.Dept, Shift_Mstr.Shift_InTime, History.EventTime, History.EventDate, Shift_Mstr.Shift_OutTime, History.EventTime
FROM History INNER JOIN (Emp_Shift_Dtls INNER JOIN Shift_Mstr ON Emp_Shift_Dtls.Shift_Code = Shift_Mstr.Shift_Code) ON History.UserID = Emp_Shift_Dtls.EMP_ID
WHERE (((History.Dept)="Engg") AND ((History.EventDate)=#2/24/2006#) AND ((History.FuncCode)="0") AND ((Emp_Shift_Dtls.Shift_Start_Date)<#2/24/2006#) AND ((Emp_Shift_Dtls.Shift_End_Date)>#2/24/2006#));

Here FunCode 0 is for IN time and 10 s for OUT time

[Rabbit]

Hi,

I m working with access.we hav a table history stores the employee IN/OUT time details...The IN/OUT time details s stored in the same field as event time....I want to get the IN time and OUT time in seperate columns..
like below


IN OUT
9:00AM 6:00 PM

the 9:00AM nd 6:00 PM s stored in the same field as event....

can u help me?

Query

SELECT DISTINCT History.UserID, History.UserName, History.Dept, Shift_Mstr.Shift_InTime, History.EventTime, History.EventDate, Shift_Mstr.Shift_OutTime, History.EventTime
FROM History INNER JOIN (Emp_Shift_Dtls INNER JOIN Shift_Mstr ON Emp_Shift_Dtls.Shift_Code = Shift_Mstr.Shift_Code) ON History.UserID = Emp_Shift_Dtls.EMP_ID
WHERE (((History.Dept)="Engg") AND ((History.EventDate)=#2/24/2006#) AND ((History.FuncCode)="0") AND ((Emp_Shift_Dtls.Shift_Start_Date)<#2/24/2006#) AND ((Emp_Shift_Dtls.Shift_End_Date)>#2/24/2006#));

Here FunCode 0 is for IN time and 10 s for OUT time

You said that In and Out time are stored in the same field as EventTime but what's this Shift_Mstr.Shift_InTime and Shift_Mstr.Shift_OutTime?

[NeoPa]

Hi,

I m working with access.we hav a table history stores the employee IN/OUT time details...The IN/OUT time details s stored in the same field as event time....I want to get the IN time and OUT time in seperate columns..
like below


IN OUT
9:00AM 6:00 PM

the 9:00AM nd 6:00 PM s stored in the same field as event....

can u help me?

Query

SELECT DISTINCT History.UserID, History.UserName, History.Dept, Shift_Mstr.Shift_InTime, History.EventTime, History.EventDate, Shift_Mstr.Shift_OutTime, History.EventTime
FROM History INNER JOIN (Emp_Shift_Dtls INNER JOIN Shift_Mstr ON Emp_Shift_Dtls.Shift_Code = Shift_Mstr.Shift_Code) ON History.UserID = Emp_Shift_Dtls.EMP_ID
WHERE (((History.Dept)="Engg") AND ((History.EventDate)=#2/24/2006#) AND ((History.FuncCode)="0") AND ((Emp_Shift_Dtls.Shift_Start_Date)<#2/24/2006#) AND ((Emp_Shift_Dtls.Shift_End_Date)>#2/24/2006#));

Here FunCode 0 is for IN time and 10 s for OUT time

Conceptually, you use the GROUP BY clause to group similar items together, then you have two fields, one which selects only records whose type is an 'IN' and the other which selects only records whose type is an 'OUT'.

[hygeena]

Thnks for Reply..But when I gave this query Its came like this

ShiftIn IN ShiftOut OUT
9:00AM 9:10 AM 6:00 PM 9;10 AM

same shift IN time is coming uder both IN and OUT....
But I got the Answer...The query S like below

SELECT DISTINCT h.UserID, h.UserName, h.EventDate, Shift_Mstr.Shift_InTime, h.EventTime AS InTime, Shift_Mstr.Shift_OutTime, h2.EventTime AS OutTime
FROM (History AS h LEFT JOIN History AS h2 ON (h.UserID = h2.UserID) AND (h.EventDate = h2.EventDate) AND (h.UserName = h2.UserName)) INNER JOIN (Shift_Mstr INNER JOIN Emp_Shift_Dtls ON Shift_Mstr.Shift_Code = Emp_Shift_Dtls.Shift_Code) ON h.UserID = Emp_Shift_Dtls.EMP_ID
WHERE (((h.EventDate)=#2/24/2006#) AND (([h.FuncCode])="0") AND (([h2.FuncCode])="10") AND ((Emp_Shift_Dtls.Shift_Start_Date)<=#2/24/2006#) AND ((Emp_Shift_Dtls.Shift_End_Date)>=#2/24/2006#));

ShiftIn IN ShiftOut OUT
9:00AM 9:10 AM 6:00 PM 6:00 PM