Query for getting value from another record in same table and filter by difference greater than a gap threshold

by

Will offer one more contribution – option with VBA code to get the Current/Previous pairs. This does require saving records to table. Tested and runs in a snap. 

Sub GetGap()
Dim intTH As Integer, x As Integer, strGID As String
Dim rsT1 As DAO.Recordset, rsT2 As DAO.Recordset
CurrentDb.Execute "DELETE FROM Temp"
Set rsT1 = CurrentDb.OpenRecordset("SELECT * FROM Temporary ORDER BY GlobalID, ItemID DESC;")
Set rsT2 = CurrentDb.OpenRecordset("SELECT * FROM Temp;")
strGID = rsT1!GlobalID
x = 1
While Not rsT1.EOF
    If strGID = rsT1!GlobalID Then
        If x = 1 Then
            rsT2.AddNew
            rsT2!GlobalID = strGID
            rsT2!CurItemID = rsT1!ItemID
            rsT2!CurDate = rsT1![Version Date]
            x = 2
        ElseIf x = 2 Then
            rsT2!GlobalID = strGID
            rsT2!PreItemID = rsT1!ItemID
            rsT2!PreDate = rsT1![Version Date]
            x = 3
        End If
        If Not rsT1.EOF Then rsT1.MoveNext
    Else
        If x = 3 Then rsT2.Update
        strGID = rsT1!GlobalID
        x = 1
    End If
    If rsT1.EOF Then rsT2.Update
Wend
End Sub

Then a query can easily calculate the Gap and filter records.

SELECT Temp.GlobalID, Temp.CurItemID, Temp.CurDate, Temp.PreDate, Temp.PreItemID, [CurDate]-[PreDate] AS Gap
FROM Temp
WHERE ((([CurDate]-[PreDate])>Int([Enter Threshold])));

Or the code can be expanded to also calc the Gap and save only records that meet the threshold requirement, just a bit more complicated.

More Related Contents:

  1. How do I use parameters in VBA in the different contexts in Microsoft Access?
  2. What’s the difference between DoCmd.SetWarnings and CurrentDB.Execute
  3. Using Excel VBA to export data to MS Access table
  4. How to use system username directly in MS Access query?
  5. Access – compare two tables and update or insert data in first table
  6. How to save the result of a SQL query into a variable in VBA?
  7. Escaping ‘ in Access SQL
  8. SQL Query output in VBA is different than in SQL Oracle
  9. Upserting in MS-access
  10. Is it possible to pass parameters programmatically in a Microsoft Access update query?
  11. How to simulate UNPIVOT in Access?
  12. SQL Query – Using Order By in UNION
  13. How do I count unique items in field in Access query?
  14. How to use parameters “@” in an SQL command in VB
  15. Top n records per group sql in access
  16. Access-SQL: Inner Join with multiple tables
  17. Address standardization within a database
  18. Does MS Access support “CASE WHEN” clause if connect with ODBC?
  19. Why does a LIKE query in Access not return any records?
  20. Achieving ROW_NUMBER / PARTITION BY in MS Access
  21. SQL Updating from an inner join
  22. How to delete in MS Access when using JOIN’s?
  23. sql query if parameter is null select all
  24. Editing Record issues in Access / SQL (Write Conflict)
  25. How do I create a table alias in MySQL
  26. Select query with date condition
  27. Does MS access(2003) have anything comparable to Stored procedure. I want to run a complex query in MS acceess
  28. Microsoft Access – Case Query
  29. CONCAT equivalent in MS Access
  30. Check if access table exists

Leave a Comment