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

    Numbering Values Grouped By an ID

    My goal today is to replicate a running count of a row based on the ID. This is what I have so far.



    Code:
              SELECT (Select COUNT(Equipment_EquipmentID) 
                            FROM Equipment_OdometerHistory as eoh2 
                            WHERE eoh2.Equipment_EquipmentID = eoh.Equipment_EquipmentID 
                            GROUP BY Equipment_EquipmentID) as Transactions,
                            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;
    Here is the current result set:

    Click image for larger version. 

Name:	Annotation 2019-05-09 110225.png 
Views:	12 
Size:	25.1 KB 
ID:	38341

    Here is the desired result set (I can get it to work easily in SSMS with the OVER clause and I feel like I am really close in Access, but just can't seem to figure it out):

    Click image for larger version. 

Name:	Annotation 2019-05-09 110255.png 
Views:	12 
Size:	6.3 KB 
ID:	38342

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Access wont.
    either do it in code or ignore it. Its not really needed to view the data.

    You can do it in a report , but a query cannot.
    In the report query, set 1 field to a constant = 1.
    then in the report ,make that field a running sum over the group, and you get your desired numbering.

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

Similar Threads

  1. Grouped concatenated values..?
    By Bcanfield83 in forum Queries
    Replies: 4
    Last Post: 03-18-2019, 05:03 AM
  2. Replies: 1
    Last Post: 09-23-2018, 07:10 AM
  3. Replies: 5
    Last Post: 10-15-2012, 12:18 AM
  4. Min/Max of Transposed and Grouped Values
    By Lady_Jane in forum Queries
    Replies: 3
    Last Post: 06-20-2011, 08:55 AM
  5. Replies: 8
    Last Post: 05-25-2010, 04:50 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