Re: Amount of time between records

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" <chowda@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:762EBCFB-DFF0-4D5A-BDB6-8FAC555297CC@xxxxxxxxxxxxxxxx
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" <chowda@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:46336456-4EA8-42A1-89AA-CDDF3C1085A7@xxxxxxxxxxxxxxxx
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!






.

Relevant Pages