I’m running the latest v5 of Ham Radio Deluxe (HRD) using MS Access as the backend database. I wanted to know what was the earliest QSL confirmation either via a paper QSL or a LoTW QSL for each DXCC. This will help with making sure I keep things as up to date with what is new since my last DXCC application update.

Here’s a snippet of the results.

The only real caveat I see is if other stations re-upload their records to LoTW as LoTW currently treats it as “new” and depending on what you use to update the LoTW QSL date (I use HRD Utilities from WD5EAE), it would show the last update by the other op and not the first. But that is out of our control and LoTW should really prevent that, as well as the other op not doing it in the first place.

I have MS Access 2007 and here’s the SQL to run. I don’t know if it works with older versions of Access.

SELECT t.COL_CALL, t.COL_TIME_ON, t.COL_COUNTRY,
 IIF(t.COL_QSL_RCVD="Y" And COL_QSLRDATE = t3.FirstConfirmation, "Paper", "Electronic") As COL_METHOD,
 t3.FirstConfirmation As COL_QSLDATE
 FROM TABLE_HRD_CONTACTS_V01 t
 INNER JOIN (
 SELECT t1.COL_COUNTRY, MIN(t1.COL_TIME_ON) As FirstContact, MIN(t2.MinDate) As FirstConfirmation
 FROM TABLE_HRD_CONTACTS_V01 t1
 INNER JOIN (
 SELECT COL_COUNTRY, Min(IIf((COL_QSLRDATE<COL_LOTW_QSLRDATE AND COL_QSL_RCVD="Y" And COL_LOTW_QSL_RCVD="Y") Or
COL_LOTW_QSL_RCVD<>"Y", COL_QSLRDATE, COL_LOTW_QSLRDATE)) AS MinDate
 FROM TABLE_HRD_CONTACTS_V01
 WHERE COL_QSL_RCVD="Y" OR COL_LOTW_QSL_RCVD="Y"
 GROUP BY COL_COUNTRY) t2
 ON t1.COL_COUNTRY = t2.COL_COUNTRY AND (t1.COL_QSLRDATE = t2.Mindate OR t1.COL_LOTW_QSLRDATE = t2.Mindate)
 GROUP BY t1.COL_COUNTRY) t3
 ON t.COL_COUNTRY = t3.COL_COUNTRY AND t.COL_TIME_ON = t3.FirstContact
ORDER BY t3.FirstConfirmation DESC;

73,
K2DSL