Ham Radio Deluxe (HRD) Query – Earliest QSLs by DXCC

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.

CALLQSO DATE TIME
COUNTRY QSL METHODQSL DATE
GU4CHY11/29/2009 3:36:57 PMGuernseyPaper12/18/2009
HP3DX10/10/2009 10:16:18 PMPanamaPaper12/12/2009
Z36W12/5/2009 2:13:02 PMMacedoniaElectronic12/7/2009
DJ3IW12/5/2009 1:00:48 PMGermanyElectronic12/6/2009
D2NX10/18/2008 6:15:11 PMAngolaPaper12/1/2009
E21YDP10/17/2009 12:31:31 PMThailandElectronic10/23/2009
ZP8VAO9/12/2009 11:04:30 PMParaguayPaper9/19/2009
T77NM5/9/2009 8:51:32 PMSan MarinoPaper9/10/2009
J5UAP3/7/2009 11:47:31 PMGuinea-BissauElectronic8/30/2009
5N0HQ7/11/2009 6:09:57 PMNigeriaElectronic8/19/2009

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