Previous Record Problem 1

[Mikeauz]

Hi,

I'm trying to produce a report that shows a subreport with Sign-in/Sign out times and the difference between the previous sign-out time and the current sign-in time.

For example:

Difference Sign-in Sign-out
08:00 09:00
01:00 10:00 12:00
00:30 12:30 14:00

Any ideas?

Thanks for any help

 

[OhioSteve]

That should be easy to do.

1)What is the data type of "sign-in" and "sign-out"? If they are date/time fields, then what is their format?

2) Does the shift ever start on one date and end on another? For example, could an employee clock in at 10:00 PM and clock out at 3:00 AM ?

3) If the answer to #2 is "yes", then do you have the fields "start date" and "end date" in the table? And if you do have them, what are their data types?

 

[Mikeauz]

OhioSteve,

1, All fields are Date Time,in the format dd/mm/yyyy

2, Nope, always same day

Thanks for the help!

 

[OhioSteve]

This example should get you started. Create a table called "table1". Add fields called "timeIn" and "timeOut". Use the same data type that your real fields use. Create a query based on table1. Paste this code into the sql view:

SELECT
table1.timeIn,
table1.timeOut,
DateDiff("n",[timeIn],[timeOut]) AS [shift length]
FROM table1;

View the query in design view and save it.

The function I am using is called "DateDiff()". It lets you subtract one date from another. The first argument tells what time unit you want: "n"= minutes, "s"= seconds, "h"= hours, "d"=days, "m"=months (i think). The third argument is the second part of the subtraction problem. The SECOND argument is the FIRST part of the subtraction problem (so they are NOT in the order that you would suspect). In MS Access, I don't think it can return fractional or decimal values. So 11:30 AM - 12: 00 noon =1 (if the unit is hours). That's why I set the unit to minutes. I assumed that you wanted to track fractions of hours.

PS: If you know alot about .NET datagrids, please read thread 855-922012.

 

[rac2]

That is a little tricky to do.

The tricky part is matching up this row and the next row.

The SignIn time of the next row is the earliest time after the SignOut time of the current row.

If we knew the SignOut time, say X, then

SELECT MIN(SignIn)
FROM InAndOutBurger
WHERE SignIn > X

This means we look at every row with a SignIn time later than X, and the minimum value in all those rows is the next sign-in time. Every row offers a value for X, the SignOut time. And every row has a next row. Well just about every row.

We can fillin the value of X for each row this way

SELECT a.SignOut,
   (SELECT MIN(SignIn)
    FROM InAndOutBurger
    WHERE SignIn > a.SignOut
   ) AS "NextSignIn"
FROM InAndOutBurger a

These two values can be used in a DATEDIFF() function.

It seems likely that you will be looking for these times for each one of a list of individuals so you will add a condition to the subquery.

SELECT a.burger_order_id, a.SignOut,
   (SELECT MIN(SignIn)
    FROM InAndOutBurger
    WHERE SignIn > a.SignOut
      AND burger_order_id = a.burger_order_id
   ) AS "NextSignIn"
FROM InAndOutBurger a

This will give you for example the time between completing the order and serving the burger.


I dont know whether Access has a facility for handling correlated subqueries. And this wont be fast even with a few dozen rows. If this is a high volume situation you may want to consider adding a column to your table for the next sign-in time and update it with the value whenever you insert a new row for the next sign-in.

While that might sound complicated it will pay off in performance and simplified reporting. For instance, the approach described by OhioSteve could be applied.

 

[OhioSteve]

Yeah, after reviewing the original post I see that I may have oversimplified the problem. Apparently he wants the difference between the last signout and this signin.

Okay, here is my revised plan:

1) Add a field to the table called "lastID". I assume that the table already has an autonumber field called "ID".

2) Create and run this update sql:
UPDATE table1 SET table1.LastID = table1!ID-1;

3) Create a select sql like this:
SELECT
table1.ID,
table1.LastID,
table1.field1,
table1.field2,
table1.timeIn,
table1.timeOut,
table1_1.timeOut
FROM table1 LEFT JOIN table1 AS table1_1 ON
table1.LastID = table1_1.ID;

This sql will look wierd because I relate a table to itself, but that is legal sql. Notice that table1_1.timeOut is the timeout from the previous record.

Now learn asp.net and help me with my datagrid!
I am really floundering with that skill!