Filter Query by earliest date per combination of two columns

[epardo87]

Hello all,



so I have a table tblCertifications with Columns FullName ; Station ; CertDate ; Status

Since this table keeps historical records, you can find combinations of FullName and Station multiple times but with different CertDate each

So I have two doubts:

• How can I make a query that filters only the most recent date of each combination of columns "FullName" and "Station"?

• In form frm30, how can I use the textbox txtOperator and selection on listbox lstStation to lookup in that query and display(without clicking any button):
  
  • The value in status column in txtStatus
  • The value in CertDate column in txtLast

[JoeM]

How can I make a query that filters only the most recent date of each combination of columns "FullName" and "Station"?

You would do this using an Aggregate (Totals) query.

1. Create a new query
2. Only add the following fields: FullName, Station, CertDate
3. Click on the Totals button (looks like a Sigma) - this will add a new Totals Row to each field, and each will have a value of "Group By" under it
4. Change the Totals Row setting under the CertDate field from "Group By" to "Max"
5. View your results

This should give you what you want.

[epardo87]

You would do this using an Aggregate (Totals) query.

1. Create a new query
2. Only add the following fields: FullName, Station, CertDate
3. Click on the Totals button (looks like a Sigma) - this will add a new Totals Row to each field, and each will have a value of "Group By" under it
4. Change the Totals Row setting under the CertDate field from "Group By" to "Max"
5. View your results

This should give you what you want.

Excellent, that works perfectly.

Now I only have the second doubt to clear up

[epardo87]

So I figured the second doubt out and if it's good for anyone, this is the working code:

=DLookUp("[Status]","[Qry-CertStat]","([Estación]='" & [Forms]![frm20MainStatus]![cmbStation] & "') And ([Operador]='" & [Forms]![frm20MainStatus]![lstDesig] & "')")