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.
CALL | QSO DATE TIME
| COUNTRY | QSL METHOD | QSL DATE |
---|
GU4CHY | 11/29/2009 3:36:57 PM | Guernsey | Paper | 12/18/2009 |
HP3DX | 10/10/2009 10:16:18 PM | Panama | Paper | 12/12/2009 |
Z36W | 12/5/2009 2:13:02 PM | Macedonia | Electronic | 12/7/2009 |
DJ3IW | 12/5/2009 1:00:48 PM | Germany | Electronic | 12/6/2009 |
D2NX | 10/18/2008 6:15:11 PM | Angola | Paper | 12/1/2009 |
E21YDP | 10/17/2009 12:31:31 PM | Thailand | Electronic | 10/23/2009 |
ZP8VAO | 9/12/2009 11:04:30 PM | Paraguay | Paper | 9/19/2009 |
T77NM | 5/9/2009 8:51:32 PM | San Marino | Paper | 9/10/2009 |
J5UAP | 3/7/2009 11:47:31 PM | Guinea-Bissau | Electronic | 8/30/2009 |
5N0HQ | 7/11/2009 6:09:57 PM | Nigeria | Electronic | 8/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