subtracting two records in a field

[Guest]

I am trying to create a new database which records the total number of
running hours a piece of machinery has accumulated. The table consists of a
date field as key and a field for the total number of hours since the machine
was new. The records are to be entered manualy at the same time each day.

In order to obtain the hours run over a 24 hour period I must subtract the
last two record. This is where I get stuck.

Can anyone help me with an expression to automaticaly select the last two
records in one field and subtract them?

 

[Guest]

I had to use the Format function as subtracting some Singles or Doubles can
cause problems like:
Debug.Print 55567.3 - 55555.5 = 11.8000000000029

If you are just using integers, you won't need it.

SELECT Format(LR.LastRun-NextLastRun, "0.00") AS NetHours
FROM
[SELECT TOP 1 tblDates.wa_date,
tblDates.HoursRun as LastRun
FROM tblDates
ORDER BY tblDates.wa_date DESC]. AS LR,
[SELECT TOP 1 tblDates.wa_date,
tblDates.HoursRun as NextLastRun
FROM tblDates
WHERE tblDates.wa_date <>
(SELECT TOP 1 tblDates.wa_date
FROM tblDates
ORDER BY tblDates.wa_date DESC)
ORDER BY tblDates.wa_date DESC]. AS NLR;

 

[Guest]

Sorry Jerry,
But now you have got me completely baffled. I was thinking that there would
be a simple answer like what they taught me in school in 1965. Perhaps I'm
going way ahead of myself as a novice here but I'll keep trying to learn,
albeit slowly. Thanks alot anyway.

I had to use the Format function as subtracting some Singles or Doubles can
cause problems like:
Debug.Print 55567.3 - 55555.5 = 11.8000000000029

If you are just using integers, you won't need it.

SELECT Format(LR.LastRun-NextLastRun, "0.00") AS NetHours
FROM
[SELECT TOP 1 tblDates.wa_date,
tblDates.HoursRun as LastRun
FROM tblDates
ORDER BY tblDates.wa_date DESC]. AS LR,
[SELECT TOP 1 tblDates.wa_date,
tblDates.HoursRun as NextLastRun
FROM tblDates
WHERE tblDates.wa_date <>
(SELECT TOP 1 tblDates.wa_date
FROM tblDates
ORDER BY tblDates.wa_date DESC)
ORDER BY tblDates.wa_date DESC]. AS NLR;

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am trying to create a new database which records the total number of
running hours a piece of machinery has accumulated. The table consists of a
date field as key and a field for the total number of hours since the machine
was new. The records are to be entered manualy at the same time each day.

In order to obtain the hours run over a 24 hour period I must subtract the
last two record. This is where I get stuck.

Can anyone help me with an expression to automaticaly select the last two
records in one field and subtract them?

 

[Guest]

It would be simple - in Excel. Unfortuantely it takes a little more effort to
do something like this in any database not just Access.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Sorry Jerry,
But now you have got me completely baffled. I was thinking that there would
be a simple answer like what they taught me in school in 1965. Perhaps I'm
going way ahead of myself as a novice here but I'll keep trying to learn,
albeit slowly. Thanks alot anyway.

I had to use the Format function as subtracting some Singles or Doubles can
cause problems like:
Debug.Print 55567.3 - 55555.5 = 11.8000000000029

If you are just using integers, you won't need it.

SELECT Format(LR.LastRun-NextLastRun, "0.00") AS NetHours
FROM
[SELECT TOP 1 tblDates.wa_date,
tblDates.HoursRun as LastRun
FROM tblDates
ORDER BY tblDates.wa_date DESC]. AS LR,
[SELECT TOP 1 tblDates.wa_date,
tblDates.HoursRun as NextLastRun
FROM tblDates
WHERE tblDates.wa_date <>
(SELECT TOP 1 tblDates.wa_date
FROM tblDates
ORDER BY tblDates.wa_date DESC)
ORDER BY tblDates.wa_date DESC]. AS NLR;

--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I am trying to create a new database which records the total number of
running hours a piece of machinery has accumulated. The table consists of a
date field as key and a field for the total number of hours since the machine
was new. The records are to be entered manualy at the same time each day.

In order to obtain the hours run over a 24 hour period I must subtract the
last two record. This is where I get stuck.

Can anyone help me with an expression to automaticaly select the last two
records in one field and subtract them?