Results 1 to 4 of 4
  1. #1
    d2ws3b is offline Novice
    Windows 10 Office 365
    Join Date
    Feb 2021
    Posts
    1

    Need Help with a query for differences in meter reading

    I am working on a desk-side Access Application that is connected to a SharePoint List that contains Fueling records for a vehicle fleet.



    Every record is uniquely identified with a no-duplicates indexed primary key record number [tbl_FuelRecs].[ID]
    Every record contains a unique indexed vehicle identification number [tbl_FuelRecs].[PlateID]
    Every record contains a numeric entry to capture the vehicle's odometer reading [tbl_FuelRecs].[ODORead]
    Every record is time-datestamped at entry via [tbl_FuelRecs].[Created]
    Every record is time-datestamped at save via [tbl_FuelRecs].[Modified]
    ... and a couple other odds and ends that don't pertain to the question.

    Every time someone enters a new fuel record they provide the PlateID and the ODORead.

    I'm trying to write a DMax query to find the MOST recent PREVIOUS fueling record for that vehicle and return the [ID] of that previous record...

    Here's what I'm trying in a single-table (no inner joins, just the single table) Access LookUp Query against [tbl_FuelRecs] where my columns are:
    [ID], [PlateID], [ODORead], [LastFuelID: DMax("ID","tbl_FuelRecs","ID<"&[ID] AND "PlateID="&[PlateID])]

    It is doesn't work, [LastFuelID] returns an error.

    Any ideas or pointers to get me moving in the right direction?

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Pointer #1 - always provide the error number and message. It provides clues. Note: I hate looking up the message for a posted number (there are thousands of them).
    #2 I believe this is what you want (your situation seems to be a classic example)
    http://allenbrowne.com/subquery-01.html
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Following on from Micron's reply, this specific link is exactly what you want: http://allenbrowne.com/subquery-01.html#AnotherRecord
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,399
    remove the square brackets - they are not required as you do not have spaces or non alphanumeric characters in your field names. And you have a mixup in your use of quotes

    LastFuelID: DMax("ID","tbl_FuelRecs","ID<" & [ID] & " AND PlateID=" & [PlateID])

    ID may not be appropriate. Assuming it is an autonumber it guarantees to be unique, but not necessarily consecutive and records may be entered in the wrong order. You could use a timestamp - but that also might not be relevant if records are entered late. Ideally you need a date field which identifies the 'when' of the record - refuel date, reading date, whatever. Alternatively perhaps the previous max odometer reading would do

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

Similar Threads

  1. Query to show differences between tables
    By daveish in forum Queries
    Replies: 12
    Last Post: 01-20-2017, 07:49 AM
  2. Meter Read Database
    By aguywithfeet in forum Access
    Replies: 4
    Last Post: 09-25-2016, 12:04 PM
  3. Replies: 9
    Last Post: 12-03-2013, 02:33 PM
  4. Meter Readings Part 2
    By libraccess in forum Reports
    Replies: 4
    Last Post: 10-27-2013, 05:38 PM
  5. Meter dillema
    By praetorianprefect in forum Database Design
    Replies: 12
    Last Post: 04-13-2012, 07:22 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