2nd to last record

[supmktg]

I am trying to determine increased or decreased performance by comparing the last completed record for each location to the 2nd to last completed record for each location. Entries are made during the month, and then completed on the last day of the month.

On occasion, a location my not get completed on time. In each case I want to only compare the last completed record to the 2nd to last completed record, even if there are uncompleted entries that are newer.

My table includes the following fields:

ID (autonumber)
Location (Integer)
Status (Integer)
Date (Date)
Complete (Yes/No)

I have no problem getting the last record, but I'm having a problem getting the 2nd to last one.

Any guidance would be much appreciated,

Sup

I've attached an mdb with sample data.

 

[John Big Booty]

Try;

DoCmd.[URL="http://msdn.microsoft.com/en-us/library/bb237964%28office.12%29.aspx"]GoToRecord[/URL] acDataForm, "YourFormName", acLast, -2

This assumes all along that your records are sorted in input order.

 

[supmktg]

John,

I appreciate the quick response. Unfortunately, I am not looking for the 2nd to last record on a form. There is no form involved.

I need to compare the last record for each location in a table to the 2nd to last record for each location, so I can evaluate whether the status trend is up, down or level.

I have built a query that returns the last record for each location. However, I am having difficulty building a query that will return the 2nd to last record for each location. One of the things stumping me has to do with a yes/no field for which I want only yes records.

Please have a look at the previously attached mdb which might clarify what I'm trying to do.

Thanks,
Sup

 

[jdraw]

Sup

Here's a routine that may be helpful. I couldn't find a way to do this in a query.

'---------------------------------------------------------------------------------------
' Procedure : SecondToLast
' Author    : Jack
' Created   : 11/3/2010
' Purpose   : From AWF -- getting data from last and second to last records.
'        This prints the data to the immediate window but you could direct it
'        elsewhere as required.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Sub SecondToLast()
Dim db As DAO.database
Dim rs As DAO.Recordset
Dim rsLoc As DAO.Recordset
Dim tbl As String
   On Error GoTo SecondToLast_Error

tbl = "tblStatus"
Dim mdate As Date
Dim mLoc As String
Dim mStatus As String
Set db = CurrentDb
Set rsLoc = db.OpenRecordset("SELECT Distinct Location from " & tbl & _
                             " Where Complete = TRUE ")

Do While Not rsLoc.EOF
Set rs = db.OpenRecordset("SELECT * from " & tbl & _
                          " Where Complete = TRUE AND Location = " & _
                          rsLoc!Location)
rs.MoveLast
Last = "Last --ID(" & rs![ID] & ") Location(" & rs![Location] & ") Date(" & rs![Date] & ") Status " & rs!Status

rs.MovePrevious
mStatus = rs![Status]
mdate = rs![Date]
mLoc = rs![Location]
S2L = "S2L===ID(" & rs![ID] & ") Location(" & mLoc & ") Date(" & mdate & ") Status " & mStatus
Debug.Print Last & vbCr & S2L & vbCrLf
rsLoc.MoveNext
Loop

   On Error GoTo 0
   Exit Sub

SecondToLast_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure SecondToLast of Module ForAWF"
End Sub

 

[supmktg]

jdraw,

Your code was extremely helpful. I have altered it slightly to write the results to a table. Here is my altered code:

Private Sub CompareStatus()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsLoc As DAO.Recordset, rstComp As DAO.Recordset
Dim tbl As String
  
   On Error GoTo CompareStatus_Error

tbl = "tblStatus"
Dim mID As Integer, mID2 As Integer
Dim mdate As Date, mdate2 As Date
Dim mLoc As Integer, mLoc2 As Integer
Dim mStatus As Integer, mStatus2 As Integer
Set db = CurrentDb
Set rsLoc = db.OpenRecordset("SELECT Distinct Location from " & tbl & _
                             " Where Complete = TRUE ")

Do While Not rsLoc.EOF
Set rs = db.OpenRecordset("SELECT * from " & tbl & _
                          " Where Complete = TRUE AND Location = " & _
                          rsLoc!Location)
rs.MoveLast
Set rstComp = db.OpenRecordset("tblCompare", dbOpenDynaset)


mID = rs![ID]
mStatus = rs![Status]
mdate = rs![Date]
mLoc = rs![Location]

rs.MovePrevious

If Not rs.BOF And Not rs.EOF Then
mID2 = rs![ID]
mStatus2 = rs![Status]
mdate2 = rs![Date]
mLoc2 = rs![Location]
Else
mID2 = 0
mStatus2 = 0
mdate2 = #1/1/1900#
mLoc2 = 0
End If

rstComp.AddNew
rstComp!Location = mLoc
rstComp!StatIDCurr = mID
rstComp!StatDateCurr = mdate
rstComp!StatCurr = mStatus
rstComp!StatIDPrev = mID2
rstComp!StatDatePrev = mdate2
rstComp!StatPrev = mStatus2

rstComp.Update

rsLoc.MoveNext
Loop

   On Error GoTo 0
   Exit Sub

CompareStatus_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure CompareStatus"
End Sub

I appreciate your help VERY MUCH!
Sup

 

[jdraw]

Glad to help.