I have a database of clients who need to pass an annual certification exam.
I have a Client table, that is linked to a Certification table, with multiple Certification rows for each attempt at the exam, with a unique date & result for each row. For each Client on the Client Table, I’d like to select the row On the Certification Table with the most current Test Date, so I can easily see their current Certification status.
The Client table key is Client Name and the Certification table Key is Client Name + Testing Date. I can change these if needed.
An example of the Certification Table below. On my Client Table, I’m creating a view with lookups to several other Tables in my base to get a complete view of Clients Status. When finding info on the Certification Table, I’d like to select the row that matches each Client on the Client table, with the most current date on the Certification Table, so I can easily see their current Certification status.
ID CLIENT TEST DT RESULT
|Client1 02-17-21 | Client1 | 2/17/2021 | Certified |
|Client1 02-20-21 | Client1 | 2/20/2021 |Not Certified|
|Client2 01-01-20 | Client2 | 1/1/2020 | Not Certified|
|Client2 03-01-21 | Client2 | 3/1/2021 | Certified|
I am currently using the Free Plan, but am considering upgrading to Pro for Non-Profits. Thanks in advance for any help you can provide!