Calc diff in time b/t 2 events which are on diff rows 2

[officework13]

i am trying to calculate the difference in time b/t 2 events which are on different rows (separate records). For example The 5 fields are as follows:
Employee# Date Time Event# Event Explanation
row 1
405 Jan1/03 9:00am - 101 Sign on
row 2
405 Jan1/03 11:45am - 102 Go for lunch
row 3
405 Jan1/03 1:30pm - 103 back in service
row 4
405 Jan1/03 6:00pm - 104 sign off

I would like to do the following
1. calculate the time b/t each consecutive event.
2. create a new table where each record has the start time of the event and end time of the event (or start time of the next event as well as the description of the next event)
3. Calculate the time b/t sign on and sign off

I am no expert in developing queries using SQL or Modules or Macros (most of my query developments are using the query design mode of ACESS) Therefore i would appreciate if you could explain a little more if i am required to program using any of the above.

sincerely
Ian.

 

[Golom]

When you need to access two different records from a table, you generally need two instances of the table so that you can retrieve the two rows at the same time. In your case

Select T1.EmployeeID, T1.TimeField,
       DateDiff ("n", T1.TimeField, T2.TimeField) As [Elapsed],
       T2.[EventExplanation]

From tbl T1 INNER JOIN tbl T2 ON T1.EmployeeID = T2.EmployeeID

WHERE T2.TimeField IN (Select MIN(TimeField) From tbl
                       Where   tbl.TimeField > T1.TimeField
                           AND tbl.EmployeeID = T1.EmployeeID)

This just selects a record in "T1" and then the next record in "T2" and computes the elapsed time.

 

[officework13]

golom

a couple items i need some clarification on.

1. If i understand you - are you saying that this requires two of the exact same tables.

2.Using access 2000 where do i put this code into - query and then SQL or do i put it as a macro or module.

3. looking at this code that you included "From tbl T1 INNER JOIN tbl T2 ON T1.EmployeeID = T2.EmployeeID" What is 'tbl' is this a new table that is created if not what is it???????????

 

[Golom]

It requires two references to the table that contains your data. The first reference (called "T1&quot will retrieve the first time and the second reference ("T2&quot will retrieve the record with the next time after the time in T1.

In query design, select "SQL View" and paste this code. Change "tbl" to the name of your table and change the field names (i.e. "EmployeeID", "TimeField", "EventExplanation&quot to the actual field names in your table.

"tbl" is the name of the table that contains your data.

 

[OhioSteve]

Here are my thoughts about this thread:

1) Learning Access by creating a timekeeping DB is a bad idea. Don't experiment on employees' paychecks. However, if your employer has absolutely ordered you to do this, you may have no choice.

2) If you must do this, then you must think about security. Perhaps a supervisor will be physically monitoring things 24/7. But if the employees swipe themselves in, you have security issues bigtime.

3) You are having difficulty with your calculations because you have designed the table poorly. You have designed it poorly because you have not decided what constitutes a record.

If one record is one cardswipe, then the "time" field will record one instant, not a time period. So remove the "-" from your thought process. Also, you may not need event explanation.

If one record is one time block, then you need to change the "time" field's name to startBlock. You need to create another field called stopBlock. Both should be date/time fields.

In either case "event#" should probably be an autonumber field. This is especially important if one record is one cardswipe. Then you will need a piece of SQL that says "go to the record with the next event#, and get the 'time' value".

I am using the term "cardswipe" euphemistically. The actual method may vary.

First decide what constitutes a record. Then we will proceed from the general to the specific.

 

[OhioSteve]

After I logged off I had a couple of other thoughts.

The autonumber field may not be so important, because we can sort the records using the time field(s).

If one record is one swipe, then finding the next record will be a bit more complicated than I had said. But I we can still work on it.

 

[officework13]

Thanks G
so far it is doing as required.