Results 1 to 4 of 4
  1. #1
    gquery is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    20

    Nulling the first Prev_Value cell when the ID changes

    Good morning everyone, I am having a little trouble figuring this out

    Code:
    SELECT eoh.Equipment_EquipmentID, e.AccountingCode, eoh.ReadingDate ,eoh.TrueReading, 
    (select TOP 1 LAG.TrueReading from Equipment_OdometerHistory as LAG     
      WHERE (LAG.Equipment_EquipmentID < eoh.Equipment_EquipmentID) or (LAG.Equipment_EquipmentID = eoh.Equipment_EquipmentID and LAG.TrueReading < eoh.TrueReading)
      GROUP BY LAG.Equipment_EquipmentID, LAG.TrueReading
      ORDER BY LAG.Equipment_EquipmentID DESC, LAG.TrueReading DESC
        ) AS Prev_Value, (eoh.TrueReading-Prev_Value) AS [Elapsed Odometer]
    FROM Equipment AS e INNER JOIN Equipment_OdometerHistory AS eoh ON e.EquipmentID = eoh.Equipment_EquipmentID
    WHERE (((e.IsDeleted)=False))
    GROUP BY eoh.Equipment_EquipmentID, e.AccountingCode, eoh.ReadingDate, eoh.TrueReading;
    The purpose of this query is to take values and lag them so that I can calculate a difference between the two values. It achieves this goal... for example:

    Click image for larger version. 

Name:	Annotation 2019-05-07 101933.png 
Views:	7 
Size:	31.2 KB 
ID:	38313

    The only part that I am having trouble with is making the previous value and elapsed odometer cells null when the EquipmentID changes as underlined in the below image.

    Click image for larger version. 

Name:	Annotation 2019-05-07 102022.png 
Views:	7 
Size:	32.6 KB 
ID:	38315

    Can this be done? Thanks ahead of time for any help!
    Attached Thumbnails Attached Thumbnails Annotation 2019-05-07 102059.png  

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'd expect the criteria of the subquery to be more like this:

    http://allenbrowne.com/subquery-01.html#AnotherRecord
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    gquery is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Posts
    20
    Excellent source. Works perfect now. Here is the resulting SQL.

    Code:
    SELECT eoh.Equipment_EquipmentID, e.AccountingCode, eoh.ReadingDate ,eoh.TrueReading, (select TOP 1 LAG.TrueReading from Equipment_OdometerHistory as LAG     
         WHERE (LAG.Equipment_EquipmentID = eoh.Equipment_EquipmentID) and (LAG.TrueReading < eoh.TrueReading)
         GROUP BY LAG.Equipment_EquipmentID, LAG.TrueReading
         ORDER BY  LAG.TrueReading DESC, LAG.Equipment_EquipmentID 
        ) AS Prev_Value, (eoh.TrueReading-Prev_Value) AS [Elapsed Odometer]
    FROM Equipment AS e INNER JOIN Equipment_OdometerHistory AS eoh ON e.EquipmentID = eoh.Equipment_EquipmentID
    WHERE (((e.IsDeleted)=False))
    Group By eoh.Equipment_EquipmentID, e.AccountingCode, eoh.ReadingDate, eoh.TrueReading;

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad it worked for you.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Import Excel data cell by cell into an Access table.
    By russmann2000 in forum Import/Export Data
    Replies: 3
    Last Post: 03-21-2018, 07:18 PM
  2. Clear Cell Range Based on Text in Another Cell
    By Oxygen Potassium in forum Access
    Replies: 3
    Last Post: 08-20-2017, 08:12 PM
  3. Replies: 1
    Last Post: 07-12-2013, 01:48 PM
  4. Replies: 12
    Last Post: 01-15-2013, 02:35 PM
  5. Can we post Access cell data to Excel cell properties?
    By Zethro in forum Import/Export Data
    Replies: 1
    Last Post: 12-13-2005, 08:42 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
  •  
Other Forums: Microsoft Office Forums