Results 1 to 3 of 3

Query to populate a new field with values from an existing field based on multiple criteria

  1. #1
    Sprwight is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    2

    Query to populate a new field with values from an existing field based on multiple criteria

    Hello



    I have data from a table that looks like this:

    Click image for larger version. 

Name:	Untitled.jpg 
Views:	10 
Size:	40.0 KB 
ID:	28690

    I would like to run a query to add two more fields:
    NewField1 = [PrevHmKm] which is the next highest value from [CurHmKm] for the same [PlantNo]
    NewField2 = [WorkHr] which is [CurHmKm] - [PrevHmKm]

    The end result should be a run query that look like this:

    Click image for larger version. 

Name:	Untitled2.jpg 
Views:	10 
Size:	63.3 KB 
ID:	28691

    How do I do this?

  2. #2
    alansidman's Avatar
    alansidman is offline Mountain Living Proponent
    Windows 10 Access 2016
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,429
    Attached is a db, I developed for a friend to track gas mileage. It uses the principle you are looking for. Open the query search form. Click on the MPG button and then look at the query in design form to see how to accomplish this.
    Attached Files Attached Files

  3. #3
    Sprwight is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    May 2017
    Posts
    2
    Thanks for the db
    Great example
    In my case, I have multiple 'cars' and the transactions are not added to the table in date or mileage order so making use of the record ID-1 to find the prior mileage does not work. That, and even if the records were in date and mileage order, the ID-1 will refer to a different 'car'.
    What is the way around this?

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

Similar Threads

  1. Replies: 7
    Last Post: 06-03-2015, 11:42 AM
  2. new query field based on existing field logic
    By sfgiantsdude in forum Access
    Replies: 3
    Last Post: 10-08-2014, 04:24 PM
  3. Replies: 5
    Last Post: 07-29-2014, 11:20 AM
  4. Replies: 29
    Last Post: 04-25-2014, 03:49 PM
  5. Replies: 2
    Last Post: 05-16-2012, 04:45 PM

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