Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102

    Calculating miles between previous and current record.


    I have searched the net but not been able to get my head round this one,
    I have a table of work carried out on a vehicle, fields include date of job, mileage or hours of vehicle when job was done etc, I would like to calculate how many miles travelled between previous job and current job.

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    See Allen Browne's Get the value in another record : http://allenbrowne.com/subquery-01.html

  3. #3
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Hi there aytee111 yes I saw that one but couldn't get it to work. Any suggestions?

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    What is your SQL for the query to get the current mileage?

  5. #5
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    This is what I have, using Allen Browne code, I'm not sure what to replace the 'addressid' with,

    SELECT tblworkdone.unitID, 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.unitID)
    AS PriorValue
    FROM tblworkdone;

  6. #6
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    SELECT tblWorkdone.Unitid, tblWorkdone.Inspected, tblWorkdone.NewJobDate, tblWorkdone.MileageHours
    FROM tblWorkdone
    WHERE (((tblWorkdone.Inspected)=Yes) AND ((tblWorkdone.MileageHours) Is Not Null))
    ORDER BY tblWorkdone.Unitid, tblWorkdone.NewJobDate DESC;

  7. #7
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Does that query not give you the prior value? The 'addressID' is the primary key of the table and you have accomplished that.

  8. #8
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You can add this:

    ....AS PriorValue, tblWorkdone.MileageHours-PriorValue AS Diff....

  9. #9
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Hi it runs but a message pops up saying will only return one item and I get #name in the one record returned from the query

  10. #10
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    This is the exact message, 'at most one record can be returned by this subquery' then only the one record is displayed but all fields have #name in them.

  11. #11
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    There must be something in your data?

    BTW, addressID is not the primary key, it is the field that ties your records together, so would be your vehicle ID (unitID?). The subquery is sorting by the primary key, not the vehicle unitID.

    Can you provide an example of what your data looks like?

  12. #12
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Ok so is 'ID in allen Brownes example the primary key? if it is I can change that to see if that works.

  13. #13
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    I know have substituted 'ID' for the primary key and seems to be working but because of the record count it is taken forever to display, I will filter it down to the category I want.

  14. #14
    tonycl69 is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Nov 2012
    Posts
    102
    Right it is now working thanks for that, I just put the wrong ID in the wrong place. Thanks for your help, again.

  15. #15
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Sometimes it is quicker to make two (or more) separate queries instead of using a subquery. Hint: Name them all the same (with a,b,c or 1,2,3) otherwise you can get lost in a nav pane with a large number of objects.

Page 1 of 2 12 LastLast
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