Retrieving the 2nd highest value from a set of records in a table

[Ina]

I'm still very new in this. I manage to figure this out by looking at previous threads regarding 2nd value.
I have a table called ReproductionT and I want a record where the WhelpingDate value is the 2nd highest.
I have an Unbound Textbox in a subform called PrevWhelpingDate.


This event runs from the subform PostPartumSubF.

Private Sub Form_Load()
DoCmd.SetWarnings True
DoCmd.RunSQL "SELECT Max(WhelpingDate) AS PrevWhelpingDate FROM ReproductionT WHERE "" " & _
"And MotherID = "" & MotherID AND WhelpingDate < (Select " & _
"Max(WhelpingDate) FROM ReproductionT)"
DoCmd.SetWarnings False
End Sub

I also have this in my main form:
Private Sub Form_Load()
PostPartumSubF.Requery
End Sub
Not sure if I have the right Event Procedures and how do I get the value to show in the textbox in the subform.
It doesn't show a value when I open the form.

[Bob Fitz]

Can you post a copy of the db.

[CJ_London]

surprised that actually works - there is a superfluous " after the WHERE

SELECT Max(WhelpingDate) AS PrevWhelpingDate FROM ReproductionT WHERE " And MotherID = " & MotherID AND WhelpingDate < (Select Max(WhelpingDate) FROM ReproductionT)

You also need to alias the table in the subquery

(Select Max(WhelpingDate) FROM ReproductionT AS RT)

I want a record where the WhelpingDate value is the 2nd highest

for all records? which is what you are doing with


(Select Max(WhelpingDate) FROM ReproductionT)

or do you want the one for that particular MotherID which would require

(Select Max(WhelpingDate) FROM ReproductionT AS RT WHERE MotherID=ReproductionT.MotherID)

[kd2017]

UNTESTED

You could do something like this: just make a function that looks up the top 2 dates and then returns the earlier of the two dates.

Private Function LookupPrevWhelpingDate(MotherID As Long)
On Error GoTo ErrHandler
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim qry As String
    Dim rslt As Variant
    
    qry = "SELECT TOP 2 WhelpingDate FROM ReproductionT WHERE MotherID = " & MotherID & " ORDER BY WhelpingDate ASC;"
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset(qry)
    
    If Not (rs.BOF And rs.EOF) Then
        rs.MoveLast
        If rs.RecordCount > 1 Then
           rs.MoveFirst
           rslt = rs!WhelpingDate
        End If
    End If
    
ExitHandler:
    Set rs = Nothing
    Set db = Nothing
    LookupPrevWhelpingDate = rslt
    Exit Function

ErrHandler:
    MsgBox Err.Description, , "Error #" & Err.Number
    Resume ExitHandler
End Function

And then set the control source of the textbox to something like =LookupPrevWhelpingDate(MotherID)

[Ina]

Thank you for your reply, Bob. My zip file is 7mb which is too large to attach.
I can give you access to my One Drive but I'm not sure if it's safe to do so. I don't want to make it available for anyone to access.
Let me know what the safest way is to do this.
I appreciate your help

[Ina]

Hi kd2017
I tried your function and I get an error no matter how I present it.
I want the results for the Motherid that has been passed to the form. What should go in the brackets?

[Micron]

My zip file is 7mb which is too large to attach.

1) Remove tables, queries, forms, reports not needed to solve your problem.
2) test to make sure the necessary forms, etc. will run/open with what's left
3) compact/repair and zip again.
If it's still too large, you must have embedded images in there. Could delete those too.

[kd2017]

Until you can figure out how to upload your db file I'm afraid I can't be much more help. Here is an example db implementing the code I posted above (had to make a couple tweaks).
example.zip

[CarlettoFed]

Try this code:

Private Function LookupPrevWhelpingDate(MotherID As Long) as Date
    On Error GoTo ErrHandler
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT WhelpingDate FROM ReproductionT WHERE MotherID = " & MotherID & " ORDER BY WhelpingDate")
    If rs.RecordCount > 0 Then
        rs.MoveLast
        If rs.RecordCount > 1 Then
           rs.MovePrevious
           LookupPrevWhelpingDate= rs!WhelpingDate
        End If
    End If
    
ExitHandler:
    Set rs = Nothing
    Set db = Nothing
    Exit Function

ErrHandler:
    MsgBox Err.Description, , "Error #" & Err.Number
    Resume ExitHandler
End Function

[Ina]

Dogs.zipHi kd2017, thank you for sending that example. I got it right. I have another field in the same table of which I need the value of. It is part of the same record which I'm getting the PrevWelpingDate from. I'm not sure how to do this as it is a number field, MLitterCount. The form is called PostPartumF
I have a function I got from someone to work out current age. It works hundred percent on a single form but with a continuous form it calculates the first record and put the same value in all the other records. It could be just something small. The form is called UpcomingWhelpingF.
It took me the whole day to reduce my db and I had to remove so much stuff to get under 2mb.
Any help is very much appreciated 😍

[moke123]

Seems like an overly complicated function to get an age.

You are using an unbound textbox in your form. I believe you'll need to use a bound control and include the function in the forms source query.

[Micron]

I'm a bit confused. You say you want the latest date but you're ordering descending (latest first, earliest last) then moving last and getting that date. Would that not then be the earliest date that your function retrieves? To get the other value from the same set of records, add the field to your recordset and you'll be able to retrieve it. Not sure your db is completely normalized but I have to go out so I can't take the time to study it now.

EDIT - I don't see why you have an IIF function in the control when you could just use the function that's generating the date. The function can deal with a null result and return anything you want if that is the case. However, your function should have a return data type and be more like

Function LookupPrevWhelpingDate(MotherID As Variant) As theTypeYouWantHere

The variant will allow you to process null. If you want to be able to return null, use Variant as the return type. Otherwise, you could type it as a string since you're currently using the IIF function to enter a zls (zero length string) anyway.

[orange]

Ina,
Something you might consider, if you select the top 1 of something, and then select the Top 1... where the value is not in (select the top 1...) you will get the second top record for the criteria.

That is, if X is the Top 1 record, then selecting the Top 1 record from the table where the value is not in (X), you will get the second highest/latest record.

[Ina]

Micron & Orange
The code you refer to was given to me by someone else in this thread. I'm not having any luck getting the second value as I'm not yet very familiar with code.
Could you please be so kind and look at my code and correct it for me, plse. The other value I need is the sum of Males+Females from the same record.
Very much appreciated!
I'm attaching a small version of my db.

Dogs.zip

[moke123]

I have a function I got from someone to work out current age. It works hundred percent on a single form but with a continuous form it calculates the first record and put the same value in all the other records. It could be just something small. The form is called UpcomingWhelpingF.

See if this solves your unbound field in a continuous form problem.

edit: am I missing something? It appears you have formatted dates as dd/mm/yyyy but in some instances they are appearing as mm/dd/yyyy.