Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / New Users / March 2006

Tip: Looking for answers? Try searching our database.

subtracting two records in a field

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
agvd - 28 Mar 2006 00:30 GMT
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?
Jerry Whittle - 28 Mar 2006 02:46 GMT
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;

Signature

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
[quoted text clipped - 6 lines]
> Can anyone help me with an expression to automaticaly select the last two
> records in one field and subtract them?
agvd - 30 Mar 2006 04:15 GMT
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:
[quoted text clipped - 27 lines]
> > Can anyone help me with an expression to automaticaly select the last two
> > records in one field and subtract them?
Jerry Whittle - 30 Mar 2006 05:51 GMT
It would be simple - in Excel. Unfortuantely it takes a little more effort to
do something like this in any database not just Access.
Signature

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
[quoted text clipped - 33 lines]
> > > Can anyone help me with an expression to automaticaly select the last two
> > > records in one field and subtract them?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.