Adding. Impossible on a form or query

[Alfred]

Hi
I have a form with follwing data
Kilometer reading
5
8
11
I would like to do the following (on the form or in the query)
kilometer r difference
5
8 3
12 4

Is this possible on a form or query ?
Thanks Alfred

 

[Mike Mueller]

: Hi
: I have a form with follwing data
: Kilometer reading
: 5
: 8
: 11
: I would like to do the following (on the form or in the
: query) kilometer r difference
: 5
: 8 3
: 12 4
:
: Is this possible on a form or query ?
: Thanks Alfred

Yes, and quite easily. I am using field names of Kilo_1,
Kilo_2, and Kilo_3 for your readings 5, 8, & 12
respectively. Put in 2 text boxes ( I will call them Text4
and Text5). Set the control of Text4 to "=[Kilo_2] -
[Kilo_1]" and Text5 to "+ [Kilo_3] - [Kilo_2]" (No quotes).

 

[Michel Walsh]

Hi,


SELECT a.kilometer, Min( a.kilometer-Nz(b.kilometer, 0 ) ) as Difference
FROM MyTable As a LEFT JOIN MyTable As b
ON a.kilometer > b.kilometer
GROUP BY a.kilometer
ORDER BY a.kilometer


and that should work even if you have thousand of records. Base your form on that query, rather
than on the initial table.


Hoping it may help,
Vanderghast, Access MVP