Results 1 to 4 of 4

Subtract 2 numbers based on date

  1. #1
    dfoster is offline Novice
    Windows 8 Access 2016
    Join Date
    Apr 2019
    Posts
    6

    Subtract 2 numbers based on date

    Vehicle maintenance database, n00b here. I have two tables. Table1 lists vehicles by field License (with other details). Table2 lists maintenance items, which includes the field License from the other table, as well as fields for Date and Odometer. I need to calculate the distance travelled by each vehicle in a quarter. So, I'm trying to figure out how to find the two records dated 12/31/2018 and 3/31/2019, subtract the Odometer values to determine the difference, and spit out a query/report with two fields: License and Miles Travelled. So, a list of all vehicles and how far they travelled in the designated time span. Help?

    (Also, I updated my profile, but it doesn't seem to update this post. I'm using Windows 10, and Access 2007)

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    47,677
    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
    dfoster is offline Novice
    Windows 10 Access 2007
    Join Date
    Apr 2019
    Posts
    6
    Whoa. That's well over my head. But I'll do my best to suss it out, thank you.

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

    SELECT *, (SELECT TOP 1 Odometer FROM Mileage AS Dupe WHERE Dupe.VehicleLicense=Mileage.VehicleLicense AND Dupe.ReadDate<Mileage.ReadDate AND Dupe.ReadDate BETWEEN #12/31/2018# AND Dupe.ReadDate=#3/31/2019# ORDER BY Dupe.ID) AS QtrBeginOR
    FROM Mileage WHERE ReadDate=#3/31/2019#;

    Note a unique record identifier field is needed ([ID]) - autonumber should serve.
    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.

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

Similar Threads

  1. Replies: 1
    Last Post: 11-27-2017, 02:02 PM
  2. Replies: 2
    Last Post: 10-11-2017, 09:33 PM
  3. Add or Subtract Business Days from a Given Date
    By orange in forum Code Repository
    Replies: 2
    Last Post: 05-15-2017, 12:45 PM
  4. Subtract From Previous Record Using a Date
    By txrules in forum Queries
    Replies: 1
    Last Post: 12-30-2010, 02:10 AM
  5. Replies: 9
    Last Post: 03-19-2010, 10:37 AM

Tags for this Thread

Posting Permissions

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