query to return previous date

[Erick]

I have a table with a date field (dates when file had been backed up). The backing up of files doesn't happen everyday so there will be gaps in the date sequence.

what I need is to write a query to return for every entry (row), the previous back up date.

any idea how this can be done?

 

[Norie]

Can't you just use Max?

That should get you the most recent backup date for each row.

If that's not what you need post back with further details.

 

[jas0501]

If by "previous backup date" you mean more recent then Max would do the trick.
--------------------------------------
If however you mean, not the more recent but the one before that, then this is a bit more difficult. You might consider, when creating the backup record, linking it to the prior, so the the more recent record contains the ID or the previous backup record. Then you can use Max to find the most recent and use the prior ID to get the "previous backup date", or any other information for that matter.

If all you need is the prior date, then add "prior backup date" as a field to the table, and when creating a new backup record include the previous backup date. Then MAX(back update) will give you the latest record, and "prior backup date" the "previous date"

 

[Erick]

thanks for the reply. I am actually looking at a historical log, so the dates are already there. The log contains back up dates for a number of machines (all in 1 log). I was hoping to write a query so that for each record date & machine, I can match the previous date it was backed up. so essentially I need to look up the maximum date that is less than the row date for the machine... have i thoroughly confused everyone yet?

 

[boblarson]

This function will return the second to last date for a particular ID. You will have to decide how to handle cases where there are no dates (if any) or just one item and there isn't a previous. But this might get you started (Paste this function into a STANDARD MODULE and save the module name with something OTHER than the function name. You should be able to paste it in and not change anything inside it):

Function GetPrevDate(lngID As Long, strDateFieldName As String, strIDFieldName as String, strTableName As String) As Date
    Dim strSQL As String
    Dim rst As DAO.Recordset
    Dim dteHold As Date
 
    strSQL = "Select Top 2 " & strDateFieldName & " From " & strTableName & " Where " & strIDFieldName & " = " & lngID & " Order By " & strDateFieldName & " Desc"
 
    Set rst = CurrentDb.OpenRecordset(strSQL)
 
    If Not rst.BOF And Not rst.EOF Then
       rst.MoveLast
       rst.MoveFirst
    End If
 
    If rst.RecordCount > 1 Then
       Do Until rst.EOF
           dteHold = rst.Fields(strDateFieldName).Value
           rst.MoveNext
       Loop
    End If
 
    GetPrevDate = dteHold
 
    rst.Close
    Set rst = Nothing
 
End Function

So in a query you would pass the record ID field as the field, date field name as string, Record ID field name as sting, and table name as string:

PreviousDate: GetPrevDate([YourIDFieldHere], "YourDateFieldNameHere", "YourIDFieldNameHere", "YourTableNameHere")

 

[Norie]

Erick

So you don't want the most recent date you want the date before that.

You could set up a query to get the max date for each machine, that will be the latest backup I think.

Then create a query that returns all the dates for each machine that don't equal the max date.

Then get the max date for each machine from those dates.

Does that make sense or am I just wittering?

Sounds logical to me, and I've done some testing and it seems to work.

Mind you the data I tested wasn't a great set to work with and perhaps a wee bit too large to check right now.

I'll have another look and see if I can post something a bit more sensible tomorrow.

 

[Erick]

Erick

So you don't want the most recent date you want the date before that.

You could set up a query to get the max date for each machine, that will be the latest backup I think.

Then create a query that returns all the dates for each machine that don't equal the max date.

Then get the max date for each machine from those dates.

Does that make sense or am I just wittering?

Sounds logical to me, and I've done some testing and it seems to work.

Mind you the data I tested wasn't a great set to work with and perhaps a wee bit too large to check right now.

I'll have another look and see if I can post something a bit more sensible tomorrow.

that's right norie, i need the previous date. that is why i was thinking i somehow need to get the max date that is less than the record date.
I have pasted below a table of how I expect the query to comeback. I have also included how this can be achieved in excel, in case it inspires a brain wave.

Date Machine Previous Date
10/08/2001 MC1
13/08/2001 MC1 =IFERROR(LOOKUP(2, 1/(B3= B$2:B2), A$2:A2),"")
13/08/2001 MC2
14/08/2001 MC1 13/08/2001
14/08/2001 MC2 13/08/2001
15/08/2001 MC2 14/08/2001
16/08/2001 MC1 14/08/2001
17/08/2001 MC1 16/08/2001
17/08/2001 MC2 15/08/2001
20/08/2001 MC1 17/08/2001
21/08/2001 MC2 17/08/2001

 

[boblarson]

So I take it you haven't tried my function?

 

[gaj104]

You can do this doing a non-equitable join, joining the same table together on Machine and Date, then editing the sql to make the join date > instead of =. Then group the query together and select max.

So in my example

Table name:

tbl_MACHINES

Table columns:
TheDate (Date/Time)
Machine (Text)

SQL:

SELECT A.THEDATE,
A.MACHINE,
MAX(A.PD) AS PREVIOUSDATE
FROM (SELECT TBL_MACHINES.THEDATE,
TBL_MACHINES.MACHINE,
TBL_MACHINES_1.THEDATE AS PD
FROM TBL_MACHINES
LEFT JOIN TBL_MACHINES AS TBL_MACHINES_1
ON ( TBL_MACHINES_1.MACHINE = TBL_MACHINES.MACHINE )
AND ( TBL_MACHINES.THEDATE > TBL_MACHINES_1.THEDATE )) AS A
GROUP BY A.THEDATE,
A.MACHINE;

Works pretty nicely and no need for a udf

 

[boblarson]

You can do this doing a non-equitable join, joining the same table together on Machine and Date, then editing the sql to make the join date > instead of =. Then group the query together and select max.

So in my example

Table name:

tbl_MACHINES

Table columns:
TheDate (Date/Time)
Machine (Text)

SQL:

SELECT A.THEDATE,
A.MACHINE,
MAX(A.PD) AS PREVIOUSDATE
FROM (SELECT TBL_MACHINES.THEDATE,
TBL_MACHINES.MACHINE,
TBL_MACHINES_1.THEDATE AS PD
FROM TBL_MACHINES
LEFT JOIN TBL_MACHINES AS TBL_MACHINES_1
ON ( TBL_MACHINES_1.MACHINE = TBL_MACHINES.MACHINE )
AND ( TBL_MACHINES.THEDATE > TBL_MACHINES_1.THEDATE )) AS A
GROUP BY A.THEDATE,
A.MACHINE;

Works pretty nicely and no need for a udf

Yep, it sure does work. Good one.