By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,647 Members | 1,444 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,647 IT Pros & Developers. It's quick & easy.

To display IN time and OUT time in seperate fields

P: 5
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

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT History.UserID, History.UserName, History.Dept, Shift_Mstr.Shift_InTime, History.EventTime, History.EventDate, Shift_Mstr.Shift_OutTime, History.EventTime
  2. 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
  3. 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
Feb 26 '07 #1
Share this Question
Share on Google+
3 Replies


Rabbit
Expert Mod 10K+
P: 12,324
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

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT History.UserID, History.UserName, History.Dept, Shift_Mstr.Shift_InTime, History.EventTime, History.EventDate, Shift_Mstr.Shift_OutTime, History.EventTime
  2. 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
  3. 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?
Feb 26 '07 #2

NeoPa
Expert Mod 15k+
P: 31,261
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

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT History.UserID, History.UserName, History.Dept, Shift_Mstr.Shift_InTime, History.EventTime, History.EventDate, Shift_Mstr.Shift_OutTime, History.EventTime
  2. 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
  3. 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'.
Feb 26 '07 #3

P: 5
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
Feb 27 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.