Results 1 to 9 of 9
  1. #1
    Julubasa is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    3

    I want to calculate differences of two rows from the same column.

    I have a table Pic3Tb, with 3 fields: ID, Date, and Result I want to make a query with a new column that shows the difference between the same result, from the current and last day. In excel i just to use Match Funtion.

    Pic3 table

    Id Date Result Last (ideal result)
    1 05/01/21 130 0
    2 06/01/21 150 0
    3 07/01/22 150 1 Date (Id 2) - Date(Id 3)
    4 08/01/21 130 3 Date (Id 1) - Date(Id 4)
    5 09/01/21 162 0
    6 10/01/21 562 0


    7 11/01/21 162 2 Date (Id 5) - Date(Id 7) and so on...

    Help Please!!!

  2. #2
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529
    Look at this link and pay attention to post #12

  3. #3
    Julubasa is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    3
    I can't see the link? Post12?

  4. #4
    A S MANN is offline Advanced System Analyst
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    India
    Posts
    161
    You need to create a VBA function() which will go first row( i=1) and take its value and than in loop (I=2 to Last Row) go to each row and compare its value. If values are same than take difference between ID as output saved as result.
    It will repeat the process for ( i=2) and so on.
    You need to create Two loop.

  5. #5
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Since your dates and ids are both consecutive I can’t work out what the ‘last’ actual means. Is it the difference between 2 and 3 ids? Or the number of days?

    And what happens if there is a third 130 result in say record 8? Do you still want the 3 last for record 4? And what is reported for record 8? Compare to record 1 or 4? Or start again - I.e you only ever compare 2 results

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I took it to mean DateDiff: Date (Id 2) - Date(Id 3) where Result is the same, but yes, is difference in days or what?

    2 06/01/21 150
    3 07/01/22 150

    I also wonder if there will ever only be no more than 2 values for any given "result". Was hoping that @alansidman would come back with that link...
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    Julubasa is offline Novice
    Windows 10 Office 365
    Join Date
    Apr 2021
    Posts
    3
    Thanks for your fast response!
    Do you still want the 3 last for record 4? , yes, i mean in the record 4 I take the Id Date 1 of the record 1 (The earlier record) - the Id date 4 of record 4 (the current record)
    what happens if there is a third 130 result in say record 8? In this instance I will take the Id Date 4 of the record 4 (The earlier record) - the Id date 8 of record 8 (the current record)
    can You help me with the VBA faction code Please?

    Thanks for advance...


  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Ok - be aware that date and last are reserved words and should not be be used as a field names

    So since dates are in the same order as ids the dates column is irrelevant - use a subquery. Something like

    Select id, date, result, Id - nz(select max(Id) from pic3 where result=a.result and Id <a.Id).a.Id) as last
    From pic3 a
    Order by a.Id

  9. #9
    A S MANN is offline Advanced System Analyst
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    India
    Posts
    161
    You can check the double loop code as given in form1. You need to put your condition inside loop. hope that will solve your issue.
    Attached Files Attached Files

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

Similar Threads

  1. Replies: 4
    Last Post: 01-07-2019, 05:20 PM
  2. Replies: 1
    Last Post: 04-05-2018, 02:10 PM
  3. How to calculate differences
    By ghodgkins in forum Access
    Replies: 5
    Last Post: 06-24-2015, 12:01 PM
  4. Replies: 1
    Last Post: 09-07-2011, 02:42 AM
  5. Crosstab Query - Column differences conflict
    By JoshZulaica in forum Queries
    Replies: 1
    Last Post: 06-23-2011, 01:43 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