Page 2 of 2 FirstFirst 12
Results 16 to 18 of 18
  1. #16
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Thanks everyone for your help, although I originally put this has solved, I have hit a snag and would appreciate some help.
    The original question was to calculate mileage between previous entry and current, which was solved excellently. And now works, if there is a previous record mileage entry. What I have noticed is, the query doesn't not produce a difference if there isn't a entry in the immediate previous entry, it could be several entries earlier. Is there an additional code etc that could look for a previous mileage entry and not the immediate entry, hope that makes sense.



    This is the code I have thus far and works fine, but needs to be able to calculate on a previous date not immediately previous.

    SELECT qry_RRVHoursDiffFeed.Unit, qry_RRVHoursDiffFeed.UnitName, tblworkdone.newjobdate, tblworkdone.mileagehours, (SELECT TOP 1 Dupe.mileagehours FROM tblworkdone AS Dupe WHERE Dupe.unitid = tblworkdone.unitid AND Dupe.newjobdate < tblworkdone.newjobdate ORDER BY Dupe.newjobdate DESC, Dupe.vehmainid) AS PriorValue, tblWorkdone.MileageHours-PriorValue AS Diff
    FROM qry_RRVHoursDiffFeed INNER JOIN tblworkdone ON qry_RRVHoursDiffFeed.Unitid = tblworkdone.Unitid
    WHERE (((tblworkdone.mileagehours) Is Not Null))
    ORDER BY qry_RRVHoursDiffFeed.Unit, tblworkdone.newjobdate DESC;

  2. #17
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Add this:

    (SELECT TOP 1 Dupe.mileagehours FROM tblworkdone AS Dupe WHERE Dupe.unitid = tblworkdone.unitid AND Dupe.newjobdate < tblworkdone.newjobdate AND Dupe.mileagehours > 0

  3. #18
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Excellent, just the job. Thanks for your help, hopefully this will help others with this one too.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 08-17-2016, 07:13 AM
  2. Replies: 3
    Last Post: 03-02-2016, 09:39 AM
  3. Replies: 3
    Last Post: 09-19-2014, 08:22 AM
  4. Replies: 2
    Last Post: 03-05-2014, 05:37 PM
  5. Calculating Current Stock in Inventory/Warehouse
    By yohansetiawan in forum Access
    Replies: 5
    Last Post: 03-14-2012, 09:05 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums