Finding second unique MAX value

[PeterZ7]

Hi everyone. I'm new to Access and don't use it frequently. I've taught myself how to use MAX and MIN values in a query with some success, but I have a user request that's a bit more tricky. For the simplified version of a table below, I need to create a query that will return the SECOND most recent unique SoldDate along with all the listed fields.

tblTransactions
Fields:
CustNumber
TransNumber
SoldDate
ProductName
Price

The table consists of multiple instances of TransNumbers and I need to return all data items associated with the second most recent SoldDate. I've scoured my Access For Dummies book and it's confirmed I'm a dummy. Can someone provide some insight on a solution? I have used this forum a few times in the past for far more complex issues and have found everyone's advice and guidance invaluable and I truly appreciate it. Any suggestions would be appreciated as always. Thanks so much!
-Peter

 

[JHB]

Could you provide some sample data + the wanted result?

 

[PeterZ7]

TransNumber SoldDate ProductName Price
34 06/11/17
45 06/11/17
54 06/10/17
54 06/10/17
34 06/09/17
45 06/08/17
23 06/08/17
23 06/07/17
34 06/04/17
45 06/01/17


Result: 23 06/07/17
34 06/09/17
45 06/08/17
54 06/10/17

This is a simplified snapshot. My data set is in the tens of thousands of records, changes daily, and there are numerous updates to the transnumbers which obviously would make each run of the query provide updated results based on new information entered since query last run. *** In the rare instance that there is only ONE SoldDate for a transaction number the result should return just that one record (or two of the same dates).
Thanks so much for replying to my thread and offering help. Greatly appreciated my friend.
-peter

 

[JHB]

I've made an example in the attached database, run the query "qFinalMaxMinusOne".
In your example you've included "54 06/10/17" as a result, according to your requirement "max date minus one", it shouldn't be there, because "06/10/17" is the max date for customer 54.

 

[PeterZ7]

This works fantastically! I understand the logic clearly. I've incorporated it into the database we use and it works brilliantly. I can think of other applications for this as well that will help me with future projects. Thanks very much!
Peter

 

[JHB]

You're welcome, good luck.