Results 1 to 12 of 12
  1. #1
    jwill is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Mar 2012
    Location
    MD
    Posts
    67

    Still need help calculating break time from employee login table..

    I still need help w/ this if anyone can. I'm trying to calculate break times from this table

    Tables
    staff_log_empl_num................ text
    staff_log_date....................... date/time
    time_in_time_out..................... text.... "in"; "break out"; "break in"; "out"
    staff_log_time........................ date/time

    This query (Query 1) gives me all login/logout data from employees that didn't take a break w/ no problem

    Code:
    SELECT sl1.staff_log_date, sl1.staff_log_empl_num, (staff_first_name&' '& staff_last_name) AS staff_full_name, sl1.staff_log_time, sl1.time_in_time_out, sl2.staff_log_time, sl2.time_in_time_out, (datediff("s",sl1.staff_log_time, sl2.staff_log_time)/60.00)/60 AS total_time
    FROM (staff_login AS sl1
    INNER JOIN staff_login AS sl2 ON (sl1.staff_log_empl_num = sl2.staff_log_empl_num) AND (sl1.staff_log_date = sl2.staff_log_date))
    INNER JOIN staff AS s ON sl1.staff_log_empl_num = s.staff_empl_num
    WHERE sl1.time_in_time_out = 'IN' and sl2.time_in_time_out = 'OUT' ORDER BY sl1.staff_log_date DESC;
    Here is an example of Query 1 the result set

    staff_log_date staff_log_empl_num staff_full_name sl1.staff_log_time sl1.time_in_time_out sl2.staff_log_time sl2.time_in_time_out total_time
    8/13/2012 005319 STAFFER1 STAFFER1 9:00 AM IN 5:00 PM OUT 8
    8/12/2012 000001 JEFF STAFFER 9:00 AM IN 5:00 PM OUT 8
    8/12/2012 005319 STAFFER1 STAFFER1 9:00 AM IN 5:00 PM OUT 8
    8/11/2012 005319 STAFFER1 STAFFER1 9:00 AM IN 5:00 PM OUT 8
    8/11/2012 000001 JEFF STAFFER 9:00 AM IN 5:00 PM OUT 8
    8/10/2012 005319 STAFFER1 STAFFER1 9:00 AM IN 5:00 PM OUT 8
    8/10/2012 000001 JEFF STAFFER 9:00 AM IN 5:00 PM OUT 8
    8/9/2012 005319 STAFFER1 STAFFER1 9:00 AM IN 5:00 PM OUT 8


    This query (Query 2) gives me all login/logout data from employees that did take a break w/ no problem

    Code:
    SELECT sl1.staff_log_date, sl1.staff_log_empl_num, (staff_first_name&' '& staff_last_name) AS staff_full_name, sl1.staff_log_time, sl1.time_in_time_out, sl3.staff_log_time, sl3.time_in_time_out, sl4.staff_log_time, sl4.time_in_time_out, sl2.staff_log_time, sl2.time_in_time_out, round((datediff("s", (sl3.staff_log_time - sl4.staff_log_time), (sl1.staff_log_time - sl2.staff_log_time))/60)/60,2) AS total_time
    FROM (((staff_login AS sl1
    INNER JOIN staff_login AS sl2 ON (sl1.staff_log_empl_num = sl2.staff_log_empl_num) AND (sl1.staff_log_date = sl2.staff_log_date)) INNER JOIN staff_login AS sl3 ON (sl1.staff_log_empl_num = sl3.staff_log_empl_num) AND (sl1.staff_log_date = sl3.staff_log_date)) INNER JOIN staff_login AS sl4 ON (sl1.staff_log_empl_num = sl4.staff_log_empl_num) AND (sl1.staff_log_date = sl4.staff_log_date)) INNER JOIN staff AS s ON sl1.staff_log_empl_num = s.staff_empl_num
    WHERE sl1.time_in_time_out = 'IN' and sl3.time_in_time_out = 'BREAK OUT' and sl4.time_in_time_out = 'BREAK IN' and sl2.time_in_time_out = 'OUT' ORDER BY sl1.staff_log_date DESC;
    Here is an example of Query 2 the result set

    staff_log_date staff_log_empl_num staff_full_name sl1.staff_log_time sl1.time_in_time_out sl3.staff_log_time sl3.time_in_time_out sl4.staff_log_time sl4.time_in_time_out sl2.staff_log_time sl2.time_in_time_out total_time
    6/14/2012 008548 JAISON WILLIAMS 7:06 PM IN 9:00 PM BREAK OUT 10:00 PM BREAK IN 11:59 PM OUT 3.87
    6/7/2012 005319 STAFFER1 STAFFER1 8:00 AM IN 1:45 PM BREAK OUT 3:00 PM BREAK IN 5:00 PM OUT 7.75

    I have tried doing a union and adding this WHERE clause
    Code:
    WHERE (sl1.time_in_time_out = 'IN' and sl3.time_in_time_out = 'BREAK OUT' and sl4.time_in_time_out = 'BREAK IN' and sl2.time_in_time_out = 'OUT') or (sl1.time_in_time_out = 'IN' and sl2.time_in_time_out = 'OUT' and sl1.time_in_time_out = sl3.time_in_time_out and sl2.time_in_time_out <>sl4.time_in_time_out AND sl3.staff_log_time>=sl1.staff_log_time and sl3.time_in_time_out )
    this is a sample of what I get (notice the 4 records each of the employees that took breaks. I'm trying to get just 1 record per day)

    staff_log_date staff_log_empl_num staff_full_name sl1.staff_log_time sl1.time_in_time_out sl3.staff_log_time sl3.time_in_time_out sl4.staff_log_time sl4.time_in_time_out sl2.staff_log_time sl2.time_in_time_out total_time
    7/10/2012 005319 STAFFER1 STAFFER1 9:00 AM IN 9:00 AM IN 9:00 AM IN 3:00 PM OUT 6
    7/10/2012 000001 JEFF STAFFER 9:00 AM IN 9:00 AM IN 9:00 AM IN 3:00 PM OUT 6
    7/9/2012 005319 STAFFER1 STAFFER1 9:00 AM IN 9:00 AM IN 9:00 AM IN 3:00 PM OUT 6
    7/9/2012 000001 JEFF STAFFER 9:00 AM IN 9:00 AM IN 9:00 AM IN 3:00 PM OUT 6
    7/8/2012 005319 STAFFER1 STAFFER1 9:00 AM IN 9:00 AM IN 9:00 AM IN 3:00 PM OUT 6
    7/8/2012 000001 JEFF STAFFER 9:00 AM IN 9:00 AM IN 9:00 AM IN 3:00 PM OUT 6
    7/7/2012 000001 JEFF STAFFER 9:00 AM IN 9:00 AM IN 9:00 AM IN 5:00 PM OUT 8
    7/7/2012 005319 STAFFER1 STAFFER1 9:00 AM IN 9:00 AM IN 9:00 AM IN 5:00 PM OUT 8
    6/14/2012 008548 JAISON WILLIAMS 7:06 PM IN 9:00 PM BREAK OUT 10:00 PM BREAK IN 11:59 PM OUT 3.87
    6/14/2012 008548 JAISON WILLIAMS 7:06 PM IN 7:06 PM IN 10:00 PM BREAK IN 11:59 PM OUT 1.98
    6/14/2012 008548 JAISON WILLIAMS 7:06 PM IN 7:06 PM IN 9:00 PM BREAK OUT 11:59 PM OUT 2.98
    6/14/2012 008548 JAISON WILLIAMS 7:06 PM IN 7:06 PM IN 7:06 PM IN 11:59 PM OUT 4.87
    6/7/2012 005319 STAFFER1 STAFFER1 8:00 AM IN 8:00 AM IN 1:45 PM BREAK OUT 5:00 PM OUT 3.25
    6/7/2012 005319 STAFFER1 STAFFER1 8:00 AM IN 8:00 AM IN 8:00 AM IN 5:00 PM OUT 9
    6/7/2012 005319 STAFFER1 STAFFER1 8:00 AM IN 8:00 AM IN 3:00 PM BREAK IN 5:00 PM OUT 2
    6/7/2012 005319 STAFFER1 STAFFER1 8:00 AM IN 1:45 PM BREAK OUT 3:00 PM BREAK IN 5:00 PM OUT 7.75


    As you can see by this long post, this is becoming a thorn in my side. The times are captured by time stamp. Her employees enter their employee numbers on a form, specify whether they are logging in/out or going out/coming in from break and then they hit "enter". This captures their info. All of her employees do not take breaks, so I'm trying to get these two queries on one query so I can give her a report. Can ANYONE help me?



    Sincerely,


    Frustrated

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why is break time important? What about lunch time? Employees don't get paid breaks? Maybe need more values:

    "in"; "break1 out"; "break1 in"; "lunch out"; "lunch in" "break2 out"; "break2 in"; "out"
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jwill is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Mar 2012
    Location
    MD
    Posts
    67
    the break signifies their lunch time

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Oh, okay, then don't understand the results. Need to work with data if you want to provide. Follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Is the below true ?
    For each employee,
    on a particular date,
    there will be only 0 or 1 IN time,
    there will be only 0 or 1 OUT time, depending on IN time,
    there will be only 0 or 1 BREAK OUT time,
    and
    there will be only 0 or 1 BREAK IN time depending on BREAK OUT time.

    Thanks

  6. #6
    jwill is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Mar 2012
    Location
    MD
    Posts
    67
    Quote Originally Posted by recyan View Post
    Is the below true ?
    For each employee,
    on a particular date,
    there will be only 0 or 1 IN time,
    there will be only 0 or 1 OUT time, depending on IN time,
    there will be only 0 or 1 BREAK OUT time,
    and
    there will be only 0 or 1 BREAK IN time depending on BREAK OUT time.

    Thanks


    @recyan...Yes to all

  7. #7
    jwill is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Mar 2012
    Location
    MD
    Posts
    67
    @june7

    Thanks in advance. FYI I don't think it makes a difference in this instance but the actual file is a MDB file not accdb
    Attached Files Attached Files

  8. #8
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Just check out, if below gives some guidelines :

    Code:
    SELECT 
    	staff.staff_empl_num, 
    	staff.staff_first_name, 
    	staff.staff_last_name, 
    	qryBreakOutEmpDateTimeA.staff_log_date, 
    	qryBreakOutEmpDateTimeA.time_in_time_out, 
    	qryBreakOutEmpDateTimeA.staff_log_time, 
    	qryBreakInEmpDateTimeA.staff_log_date, 
    	qryBreakInEmpDateTimeA.time_in_time_out, 
    	qryBreakInEmpDateTimeA.staff_log_time
    FROM 
    	(
    		staff 
    		INNER JOIN 
    		(
    			SELECT 
    				staff_login.staff_log_empl_num, 
    				staff_login.staff_log_date, 
    				staff_login.time_in_time_out, 
    				staff_login.staff_log_time
    			FROM 
    				staff_login
    			WHERE 
    				(((staff_login.time_in_time_out)="BREAK OUT"))
    			ORDER BY 
    				staff_login.staff_log_empl_num, 
    				staff_login.staff_log_date, 
    				staff_login.time_in_time_out, 
    				staff_login.staff_log_time
    		)
    		AS qryBreakOutEmpDateTimeA 
    		ON 
    		staff.staff_empl_num = qryBreakOutEmpDateTimeA.staff_log_empl_num
    	) 
    	INNER JOIN 
    	(
    		SELECT 
    			staff_login.staff_log_empl_num, 
    			staff_login.staff_log_date, 
    			staff_login.time_in_time_out, 
    			staff_login.staff_log_time
    		FROM 
    			staff_login
    		WHERE 
    			(((staff_login.time_in_time_out)="BREAK IN"))
    		ORDER BY 
    			staff_login.staff_log_empl_num, 
    			staff_login.staff_log_date, 
    			staff_login.time_in_time_out, 
    			staff_login.staff_log_time
    	)
    	AS qryBreakInEmpDateTimeA 
    	ON 
    	(
    		qryBreakOutEmpDateTimeA.staff_log_empl_num = qryBreakInEmpDateTimeA.staff_log_empl_num
    	) 
    	AND 
    	(
    		qryBreakOutEmpDateTimeA.staff_log_date = qryBreakInEmpDateTimeA.staff_log_date
    	);

    Thanks

  9. #9
    jwill is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Mar 2012
    Location
    MD
    Posts
    67
    @recyan... it just gives me the break times of those that took breaks w/o any records of those that did not take breaks. I wanted to one of each record all on the same query. I really hope I don't have to restructure the tables and form.

  10. #10
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Build the sub-queries using the guidelines from previous post & save them:
    qryInEmpDateTime - For the In time
    qryOutEmpDateTime - For the Out time
    qryBreakOutEmpDateTime - For the BreakOut time
    qryBreakInEmpDateTime - For the BreakIn time

    Then try running below query :
    Code:
    SELECT 
    	staff.staff_empl_num, 
    	staff.staff_first_name, 
    	staff.staff_last_name, 
    	qryInEmpDateTime.staff_log_empl_num, 
    	qryInEmpDateTime.staff_log_date, 
    	qryInEmpDateTime.time_in_time_out, 
    	qryInEmpDateTime.staff_log_time, 
    	qryOutEmpDateTime.staff_log_empl_num, 
    	qryOutEmpDateTime.staff_log_date, 
    	qryOutEmpDateTime.time_in_time_out, 
    	qryOutEmpDateTime.staff_log_time, 
    	qryBreakOutEmpDateTime.staff_log_empl_num, 
    	qryBreakOutEmpDateTime.staff_log_date, 
    	qryBreakOutEmpDateTime.time_in_time_out, 
    	qryBreakOutEmpDateTime.staff_log_time, 
    	qryBreakInEmpDateTime.staff_log_empl_num, 
    	qryBreakInEmpDateTime.staff_log_date, 
    	qryBreakInEmpDateTime.time_in_time_out, 
    	qryBreakInEmpDateTime.staff_log_time
    FROM 
    	staff 
    	LEFT JOIN 
    	(
    		(
    			(
    				qryInEmpDateTime LEFT JOIN qryOutEmpDateTime 
    				ON 
    				(
    					qryInEmpDateTime.staff_log_date = qryOutEmpDateTime.staff_log_date
    				) 
    				AND 
    				(
    					qryInEmpDateTime.staff_log_empl_num = qryOutEmpDateTime.staff_log_empl_num
    				)
    			) 
    			LEFT JOIN 
    			qryBreakOutEmpDateTime 
    			ON 
    			(
    				qryInEmpDateTime.staff_log_date = qryBreakOutEmpDateTime.staff_log_date
    			) 
    			AND 
    			(
    				qryInEmpDateTime.staff_log_empl_num = qryBreakOutEmpDateTime.staff_log_empl_num
    			)
    		) 
    		LEFT JOIN 
    		qryBreakInEmpDateTime 
    		ON 
    		(
    			qryInEmpDateTime.staff_log_date = qryBreakInEmpDateTime.staff_log_date
    		) 
    		AND 
    		(
    			qryInEmpDateTime.staff_log_empl_num = qryBreakInEmpDateTime.staff_log_empl_num
    		)
    	) 
    	ON 
    	staff.staff_empl_num = qryInEmpDateTime.staff_log_empl_num;
    Thanks

  11. #11
    jwill is offline Advanced Beginner
    Windows Vista Access 2010 64bit
    Join Date
    Mar 2012
    Location
    MD
    Posts
    67
    @recyan.IT WORKED!!!!! thanks so much for your time and effort, I really appreciate it. I can now move on w/ my project . And when I get a chance I will dissect your query more to find out what in the world you did, lol.

    @june7 thanks for your effort as well

    bwt I added this to the SELECT statement to get the total hours from each row and to show null if an employee forgot to log their breaks (if anyone is interested)....

    Code:
    IIF(qryBreakOutEmpDateTime.staff_log_time is null and qryBreakInEmpDateTime.staff_log_time is null,
    (datediff("s", (qryBreakOutEmpDateTime.staff_log_time - qryBreakInEmpDateTime.staff_log_time), (qryInEmpDateTime.staff_log_time - qryOutEmpDateTime.staff_log_time))/60)/60), 
    round((datediff("s", qryInEmpDateTime.staff_log_time, qryOutEmpDateTime.staff_log_time)/60)/60,2)

  12. #12
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Glad you got it working.

    Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 8
    Last Post: 06-06-2012, 12:28 PM
  2. Need help calculating time on my report
    By jwill in forum Reports
    Replies: 5
    Last Post: 06-03-2012, 03:37 PM
  3. Calculating Time
    By jlclark4 in forum Forms
    Replies: 0
    Last Post: 04-25-2011, 09:04 AM
  4. Replies: 12
    Last Post: 02-22-2011, 03:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums