中国网络(86SQL.COM)企业邮件系统

3 Responses to “How to calculate the difference of the same field in two rows in Microsoft Access?”

1. Betty W on February 13th, 2010 at 12:47 am

   I don’t know access syntex, but I can give you my 2 cents how to achive this using one simple query using aggregation.

   convert your timestamp to minutes (timestamp – the date), then 8:56 will be 8×60 + 56 = 536, 1:15 => 795
   1:43 =>823, 6:03=>1083. If Access can handle case statement, that will be great, if not, add a column called "sign", -1 for clock in, 1 for clock out.

   select Sum (theCovertedMinutes * sign)
   from your table
   group by PMName

   You will get -536+795-823+1083 = 519 minute
   References :

2. knowitall on February 13th, 2010 at 1:02 am

   You’re right about needing sub queries, but you need to nest three deep. Flick me an email if you don’t unserstand any part of the following or it doesn’t quite qork for you and I’ll try and help as much as possible.

   I created a table called ‘Timesheet’ containing
   ‘ClockInId’ – an Autonumber field
   ‘TimeCheck’ – the time checked in or our
   ‘Who’ – the person who is clocking in/out
   ‘InOut’ – I or O

   You first need to create a query called AllStartFinish, this query links the table ‘Timesheet’ to itself linked by ‘Who’ and returns all checkins with every checkout that is greater than the checkin.

   The next query (called ‘StartAndFinish’ uses the query above and retunes every Checkin and the Checkout that is closest to the Checkin time (using the MIN function). You then get a single row for each checking and its corresponding checkout.

   The final query returns the data you want and the calculated difference in minutes and uses the query above as it’s data source. You select the person and dates you want from this query.

   This method works for multiple split shifts and shifts that cross over into the next day.

   The SQL
   AllStartFinish
   SELECT Timesheet.ClockInId, Timesheet.TimeCheck, Timesheet_1.TimeCheck
   FROM Timesheet INNER JOIN Timesheet AS Timesheet_1 ON Timesheet.Who = Timesheet_1.Who
   WHERE (((Timesheet_1.TimeCheck)>[Timesheet].[TimeCheck]) AND ((Timesheet.InOut)="I") AND ((Timesheet_1.InOut)="O"));

   StartAndFinish
   SELECT AllStartFinish.ClockInId, AllStartFinish.Timesheet.TimeCheck AS CheckIn, Min(AllStartFinish.Timesheet_1.TimeCheck) AS CheckOut
   FROM AllStartFinish
   GROUP BY AllStartFinish.ClockInId, AllStartFinish.Timesheet.TimeCheck;

   TheTime
   SELECT Timesheet.Who, StartAndFinish.ClockInId, StartAndFinish.CheckIn, StartAndFinish.CheckOut, DateDiff("n",[CheckIn],[CheckOut]) AS Minutes
   FROM Timesheet INNER JOIN StartAndFinish ON Timesheet.ClockInId = StartAndFinish.ClockInId
   WHERE (((Timesheet.Who)="John Deere"));

   The third query
   SELECT Timesheet.Who, StartAndFinish.ClockInId, StartAndFinish.CheckIn, StartAndFinish.CheckOut, DateDiff("n",[CheckIn],[CheckOut]) AS Minutes
   FROM Timesheet INNER JOIN StartAndFinish ON Timesheet.ClockInId = StartAndFinish.ClockInId
   WHERE (((Timesheet.Who)="John Deere"));
   References :

3. TheMadProfessor on February 13th, 2010 at 1:51 am

   The previous response is fine as far as it goes, but it might be even better if you take a further step back to evaluate what you’re trying to do here. If you want a robust application, identify your requirements and develop a procedure to satisfy it in a stepwise manner. This is particularly easy with Access since you have the fairly-powerful VBA scripting language available to you.

   1. Identify the categories of situations that need to be handled. In your case (for a given date), I see them as:
   a) Employees who did not clock in at all
   b) Employees who clocked in once but did not clock out
   c) Employees who clocked in and out once
   d) Employees who clocked in once and out twice
   e) Employees who clocked in twice and out once
   f) Employees who clocked in and out twice (the normal, expected case)
   g) Other cases (can they clock in and out more than twice in a day?)

   2. Determine what needs to be done in each case. For example, in c) above, do you assume they forgot to clock out and in for lunch and subtract an hour?

   Once you’ve determined the possibilities, then you can write simpler SELECTs to extract and process each of those cases rather than one big monolithic SELECT (which is vastly harder to write, debug and maintain than smaller procedural steps.) One such method would be an initial pass to identify in/out pairs and create a temporary table of durations while also identifying and addressing anomalies (’in’s without corresponding ‘out’s and vice versa).
   References :

Leave a Reply