Results 1 to 9 of 9
  1. #1
    JonathanT is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2011
    Posts
    118

    Subtract a field from the same field in a previous record in a Query


    I have a table that tracks the total length of trips over time. I want to calculate the length of each individual trip by subtracting the [TotalMileage] from the previous record. Very easy to do In Excel but I want to use Access. I've done internet searches and cannot find code that works. In the table screenshot below I want to calculate the numbers in red.


  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Review http://allenbrowne.com/subquery-01.html#AnotherRecord

    No image attached to your post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    JonathanT is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2011
    Posts
    118
    I found the code that works exactly like I want in another sample database. However when I tried the code in my database I don't get any values in the [PreviousMiles] field and I can't figure out why. I've attached my Database (Gas) and the sample database with the code I'm using. Thanks in advance.
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Consider:

    SELECT Purchased.PurchaseID, Purchased.GalPrice, Purchased.GalPurchased, Purchased.TotalPrice, Purchased.Mileage, Purchased.CurrDate, Purchased.PurchaseType, Purchased.PriceSavings, Purchased.TankSize, (SELECT Mileage FROM Purchased AS Alias WHERE CurrDate = (SELECT Max(CurrDate) FROM Purchased AS Alias2 WHERE Alias2.CurrDate < Purchased.CurrDate)) AS PreviousMiles, [Mileage]-Nz([PreviousMiles],[Mileage]) AS MilesDriven
    FROM Purchased
    ORDER BY Purchased.PurchaseID, Purchased.CurrDate, Purchased.Mileage;

    Or, based on Allen Browne's model:

    SELECT Purchased.*, (SELECT TOP 1 Dupe.Mileage FROM Purchased AS Dupe WHERE Dupe.CurrDate<Purchased.CurrDate ORDER BY Dupe.CurrDate DESC, Dupe.PurchaseID) AS PreviousMiles, [Mileage]-Nz([PreviousMiles],[Mileage]) AS MilesDriven
    FROM Purchased ORDER BY PurchaseID, CurrDate, Mileage;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    JonathanT is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2011
    Posts
    118
    I had tried Allen Browns model before. I tried your other suggestion and now the query won't accept the new code. The error is : "The syntax of the subquery in the expression is incorrect" "Check the subquery's syntax and enclose the subquery in parentheses".
    I can't figure this out

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I tested the queries with your posted data and they worked. Did you copy/paste my queries?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    JonathanT is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2011
    Posts
    118
    June7,

    I did. I tried both queries and am still getting the same error. Is there some setting in Access that allows/prevents sub-queries from running? Can you upload the DB where it is working so I can see what is wrong on my end?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    There is no special setting to enable these queries. It's just the db you posted.
    Gas.accdb
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    JonathanT is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2011
    Posts
    118
    June7
    Thanks for the updated Database. It works perfectly. For some reason I can’t figure out, when I copied your code into my original DB I keep getting syntax errors. I will continue to try to figure that out. In the interim I will your uploaded database. Thanks very much and I will mark this as solved.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 10-13-2021, 01:14 PM
  2. Replies: 5
    Last Post: 12-07-2016, 09:28 PM
  3. Replies: 7
    Last Post: 10-06-2014, 01:28 PM
  4. Replies: 12
    Last Post: 03-17-2012, 04:46 AM
  5. Subtract From Previous Record Using a Date
    By txrules in forum Queries
    Replies: 1
    Last Post: 12-30-2010, 02:10 AM

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