Amount of time between records

C

chowda

Hello.

I have a table that I need to add a column that shows the time difference(in
minutes) between records.

ID date
1 4/15/06 11:30 PM
2 4/15/06 11:00 PM
3 4/15/06 10:00 PM
4 4/15/06 6:00 PM
5 4/15/06 1:00 PM
6 4/15/06 12:00 PM
7 4/15/06 11:00 AM
8 4/15/06 10:00 AM
9 4/15/06 6:00 AM
10 4/15/06 3:00 AM
11 4/15/06 2:00 AM
12 4/15/06 12:00 AM

The query would look like this..

ID date Minutes between records
1 4/15/06 11:30 PM 30
2 4/15/06 11:00 PM 60
3 4/15/06 10:00 PM 240
4 4/15/06 6:00 PM 300
5 4/15/06 1:00 PM 60
6 4/15/06 12:00 PM 60
7 4/15/06 11:00 AM 60
8 4/15/06 10:00 AM 240
9 4/15/06 6:00 AM 180
10 4/15/06 3:00 AM 60
11 4/15/06 2:00 AM 120
12 4/15/06 12:00 AM 0

Can someone help me out with this?

Thanks!
 
K

KARL DEWEY

This will work if your IDs are sequential.

SELECT Chowda_11.ID, Chowda_11.Date,
DateDiff("n",[Chowda_11_1].[Date],[Chowda_11].[Date]) AS Expr1
FROM Chowda_11, Chowda_11 AS Chowda_11_1
WHERE (((Chowda_11.ID)=[Chowda_11_1].[ID]-1));

I usually would expect time to increase as the ID increased.
 
M

Michel Walsh

Hi,


Piece of cake if your ID are without 'hole' (which I am almost sure they are
NOT, but then, this is not a major problem, just a little bit more complex).
So, without hole, bring the table TWICE in the designer, one will get an _1
at the end of its name. Bring all the fields from the first table, in the
grid, but, from the second table, the one with _1, bring only its id field
and, for it, in the criteria line, type = [tableNameHere].[id] -1

The only thing left is then to add a computed field:

DateDiff("n", tableNameHere.[date] , tableNameHere_1.[date] )


And that is it. Well, note that you won't have the last record too, since,
technically, it does not have any record following it. We can get it, with a
join, but I suspect you would have other questions too, and I will just wait
for them, if you allow me... :)





Hoping it may help,
Vanderghast, Access MVP
 
C

chowda

Thank you for your help!

Regarding the missing record. How do I join that table to get the missing
record?

Thanks again!


Michel Walsh said:
Hi,


Piece of cake if your ID are without 'hole' (which I am almost sure they are
NOT, but then, this is not a major problem, just a little bit more complex).
So, without hole, bring the table TWICE in the designer, one will get an _1
at the end of its name. Bring all the fields from the first table, in the
grid, but, from the second table, the one with _1, bring only its id field
and, for it, in the criteria line, type = [tableNameHere].[id] -1

The only thing left is then to add a computed field:

DateDiff("n", tableNameHere.[date] , tableNameHere_1.[date] )


And that is it. Well, note that you won't have the last record too, since,
technically, it does not have any record following it. We can get it, with a
join, but I suspect you would have other questions too, and I will just wait
for them, if you allow me... :)





Hoping it may help,
Vanderghast, Access MVP



chowda said:
Hello.

I have a table that I need to add a column that shows the time
difference(in
minutes) between records.

ID date
1 4/15/06 11:30 PM
2 4/15/06 11:00 PM
3 4/15/06 10:00 PM
4 4/15/06 6:00 PM
5 4/15/06 1:00 PM
6 4/15/06 12:00 PM
7 4/15/06 11:00 AM
8 4/15/06 10:00 AM
9 4/15/06 6:00 AM
10 4/15/06 3:00 AM
11 4/15/06 2:00 AM
12 4/15/06 12:00 AM

The query would look like this..

ID date Minutes between records
1 4/15/06 11:30 PM 30
2 4/15/06 11:00 PM 60
3 4/15/06 10:00 PM 240
4 4/15/06 6:00 PM 300
5 4/15/06 1:00 PM 60
6 4/15/06 12:00 PM 60
7 4/15/06 11:00 AM 60
8 4/15/06 10:00 AM 240
9 4/15/06 6:00 AM 180
10 4/15/06 3:00 AM 60
11 4/15/06 2:00 AM 120
12 4/15/06 12:00 AM 0

Can someone help me out with this?

Thanks!
Click to expand...
Click to expand...
 
M

Michel Walsh

Hi,


You need an outer join. Edit the SQL from:

SELECT whatever
FROM table1, table2
WHERE condition


to

SELECT whatever
FROM table1 LEFT JOIN table2 ON condition


Since the 'condition' is not a simple equality, you cannot edit the query,
anymore, from the GRAPHICAL view. But if you need the graphical editor,
re-establish the initial query, temporary, make the required changes there,
then, push back the WHERE condition into a join, as shown.



Hoping it may help,
Vanderghast, Access MVP



chowda said:
Thank you for your help!

Regarding the missing record. How do I join that table to get the missing
record?

Thanks again!


Michel Walsh said:
Hi,


Piece of cake if your ID are without 'hole' (which I am almost sure they
are
NOT, but then, this is not a major problem, just a little bit more
complex).
So, without hole, bring the table TWICE in the designer, one will get an
_1
at the end of its name. Bring all the fields from the first table, in the
grid, but, from the second table, the one with _1, bring only its id
field
and, for it, in the criteria line, type = [tableNameHere].[id] -1

The only thing left is then to add a computed field:

DateDiff("n", tableNameHere.[date] , tableNameHere_1.[date] )


And that is it. Well, note that you won't have the last record too,
since,
technically, it does not have any record following it. We can get it,
with a
join, but I suspect you would have other questions too, and I will just
wait
for them, if you allow me... :)





Hoping it may help,
Vanderghast, Access MVP



chowda said:
Hello.

I have a table that I need to add a column that shows the time
difference(in
minutes) between records.

ID date
1 4/15/06 11:30 PM
2 4/15/06 11:00 PM
3 4/15/06 10:00 PM
4 4/15/06 6:00 PM
5 4/15/06 1:00 PM
6 4/15/06 12:00 PM
7 4/15/06 11:00 AM
8 4/15/06 10:00 AM
9 4/15/06 6:00 AM
10 4/15/06 3:00 AM
11 4/15/06 2:00 AM
12 4/15/06 12:00 AM

The query would look like this..

ID date Minutes between records
1 4/15/06 11:30 PM 30
2 4/15/06 11:00 PM 60
3 4/15/06 10:00 PM 240
4 4/15/06 6:00 PM 300
5 4/15/06 1:00 PM 60
6 4/15/06 12:00 PM 60
7 4/15/06 11:00 AM 60
8 4/15/06 10:00 AM 240
9 4/15/06 6:00 AM 180
10 4/15/06 3:00 AM 60
11 4/15/06 2:00 AM 120
12 4/15/06 12:00 AM 0

Can someone help me out with this?

Thanks!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating Meal Period Violations in Access 2
Adding rota 0
Calculate hours worked outside regular shift using date/time and iff condition 3
Determine if night shift by start & end time 2
Calculating hours missed from actual shift schedule using date/time 1
Total Minutes Worked Within IVR Timing 1
Compute minutes between hh:mm 2
Formula Diascrepancy in Worksheet 2

Top