If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Amount of time between records
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! |
#2
|
|||
|
|||
Amount of time between records
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. "chowda" wrote: 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! |
#3
|
|||
|
|||
Amount of time between records
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" wrote in message ... 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! |
#4
|
|||
|
|||
Amount of time between records
Thank you for your help!
Regarding the missing record. How do I join that table to get the missing record? Thanks again! "Michel Walsh" wrote: 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" wrote in message ... 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! |
#5
|
|||
|
|||
Amount of time between records
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" wrote in message ... Thank you for your help! Regarding the missing record. How do I join that table to get the missing record? Thanks again! "Michel Walsh" wrote: 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" wrote in message ... 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! |
Thread Tools | |
Display Modes | |
|
|