query the 3rd most recent item from a table

[ijit]

I have a table with a DateTime field. I would like to create a query that queries only the 3rd most recent record in the table. So there would only be one record per ID. If there is only two records for an ID, it would show nothing. I have tried a few things already, but none of them have worked quite right. I was wondering if anyone had a new idea.

 

[WayneRyan]

ijit,

Use two queries in succession.

The first is "Select Top 3 ..." <-- Ascending

The second uses "Select Top 1 ..." <-- Descending

You can use a DCount function between them to check for appropriate
number of records.

Wayne

 

[ijit]

Wow, that's the simpliest answer yet. But it would create alot of queries. I have to create 8 of these. One with the 3rd, one with the 4th, one with the 5th... That would give me 16 queries.

Would that concern you at all as far taking up alot of memory, or are queries fairly good on memory?

 

[ajetrumpet]

I have a table with a DateTime field. I would like to create a query that queries only the 3rd most recent record in the table.

Another idea that comes to mind is the .GoToRecord action for form usage. Say, opening a pre-sorted form with a criteria input box (same as query) and using the action with the criteria of the box to display the appropriate record.

Or, simply using the ORDER BY (DESC) command and selecting record #3 with the action.

 

[ijit]

Let me make sure I am getting the process right. I would make VB filter the table by the primary key, it would get this primary key from the parent form, then it would sort the table by DateTime (ORDER BY (DESC)), and then it would select record #3.

Can you give me an example of this?

 

[ajetrumpet]

The attached small sample is what I'm referring to. It is a form based on a filtered query (via parameter). When the Form opens, a user has to enter a parameter.

On the "FormLoad" event, the DoCmd.GoToRecord action takes place (offset = 3 for "3rd most recent record (by date)), and if the record does not exist, the user gets a message popup that give them the reason for the error and prompts them to enter a different parameter.

Parameter = Person Name
Query = Sorted on "EndDate" by DESC (prepares the form to select the 3rd most recent record).

There's probably more than one way to do this, but this might be the most presentable to a user (if you know what I mean).

 

[ijit]

OK, I'm back. Sorry it took me so long.

I'm wondering if instead of using a presorted form, I could use a listbox in the parent form and .GoToRecord actions in the subforms. So for example, the main form has a list box in it and I choose the second record in the list box, the subforms all have:

• 
  DoCmd.GoToRecord acDataForm, "Oven Codes", acGoTo, 2
  
  DoCmd.GoToRecord acDataForm, "Oven Codes", acGoTo, 3
  
  etc.

in the OnUpdate Event.

I tried this by the way and it didn't work, but I'm not sure if it's because I'm not on the right track or because I didn't use the right event, or something else. Let me know if you thin that will work.

I attached a simlified version of what I am trying to do. Let me know what you think!

 

[WayneRyan]

ijit,

OK, now I see what you are trying to do:

If you wanted to display your ovens as "rows", that would be very easy with
one subform and NO CODE.

But, if you want to display them as columns, with each subform having the
next most recent oven:

1) Change your listbox source to:

--> SELECT OvenCodes.OvenCodeKey, OvenCodes.OvenCode FROM OvenCodes ORDER BY OvenCodes.OvenCode;

--> That'll give us the OvenCode (not the name)


2) Add these "invisible" controls to your form:

txtOvenCode
txtDate1
txtDate2
txtDate3
txtDate4
txtDate5

This gives us an "easy to get" OvenCode and up to 5 most recent dates.

3) Fill the above using the OnClick event of your ListBox:

Dim rst As DAO.Recordset
Dim i As Integer

Me.txtOvenCode = Me.OvenCodeList

Set rst = CurrentDb.OpenRecordset("Select * " & _
                                  "From tblSPC " & _
                                  "Where OvenCodeKey = " & Me.txtOvenCode & " " & _
                                  "Order by DateTime DESC")
                                  
For i = 1 To 5: Me.Controls("txtDate" & CStr(i)) = "": Next i
While Not rst.EOF And Not rst.BOF
   If Me.txtDate1 = "" Then
      Me.txtDate1 = rst!DateTime
   ElseIf Me.txtDate2 = "" Then
      Me.txtDate2 = rst!DateTime
   ElseIf Me.txtDate3 = "" Then
      Me.txtDate3 = rst!DateTime
   ElseIf Me.txtDate4 = "" Then
      Me.txtDate4 = rst!DateTime
   ElseIf Me.txtDate5 = "" Then
      Me.txtDate5 = rst!DateTime
   End If
   rst.MoveNext
   Wend
Me.sfrmOven1.Requery <-- see the renamed objects below:
Me.sfrmOven2.Requery
Me.sfrmOven3.Requery
Me.sfrmOven4.Requery
Me.sfrmOven5.Requery

4) Rename your subforms to:

sfrmOven1
sfrmOven2
sfrmOven3
sfrmOven4
sfrmOven5

Just to be consistent, don't change the subform names, just the Access Control names.
Also, don't put the Master-Child links on them, leave them blank.

5) Each subform should have a query with three columns:

Item     Visible Criteria
======== ======= ========
*        Yes     None
OvenCode No      Forms![Oven Codes]![txtOvenCode]
DateTime No      Forms![Oven Codes]![txtDate1]    <-- then txtDate2, txtDate3, ...

That's it. It's a lot more work than ONE traditional Access subform,
but it should look like what you want.

hth,
Wayne

 

[ajetrumpet]

I could use a listbox in the parent form and .GoToRecord actions in the subforms. So for example, the main form has a list box in it and I choose the second record in the list box, the subforms all have:

• 
  DoCmd.GoToRecord acDataForm, "Oven Codes", acGoTo, 2
  
  DoCmd.GoToRecord acDataForm, "Oven Codes", acGoTo, 3
  
  etc.

in the OnUpdate Event.

I tried this by the way and it didn't work, but I'm not sure if it's because I'm not on the right track or because I didn't use the right event,

Yes, I think that will work, but you cannot use GoToRecord on "Oven Codes" because that is your main form and the data in the 3rd record is located in each subform's recordset.

You might even have to open the Subforms' clones to get this done, but not really sure about that...

 

[ijit]

OK, I'm trying it and it almost works. It is doing something, though, that I am not familiar with. When I click on the first thing in the list it works beutifully. When I click on the second, it lists NAME# erros in the text boxes. When I click on something else after that the name errors won't go away. It also gives me an error periodically that says, "Object Invalid or no longer set." I attached the updated form and so you can see I made all the changes you suggested.

Let me know what you think!

 

[WayneRyan]

ijit,

I don't have much time right now.

I moved the txtOvenCode, txtDate1, ... and made them visible.

Changed the logic on the OnClick event of the Oven ListBox.

It will only display subforms if they have data.

Your first subform the query's criteria fields set to visible, giving duplicates.

Needs a little cleanup, but it's closer to what you want.

See ya,
Wayne

 

[WayneRyan]

Oops,

Might help to attach it.

Wayne