Database Query Help

[w0ls]

I am trying to make a listing of qso's for band countries worked but not confirmed for each band. For instance when I look at the statistics and awards display for 160M it shows 202 worked, 165 verified and 37 not confirmed. I want to construct a data base query that show me the qso's for the 37 countries worked and not confirmed and only those qso's. How do I do that??

Thanks and 73's,
Harry
W0LS

[HB9BRJ]

Harry, this is how I would do it. Maybe there are more clever ways...

- QSO Manager > QSO Archive
- Search parameters (bottom of window)
- add 2 simple criteria with the green plus sign. Select fields and values from drop-down lists:
- Band equals 160m
- QSL Rcvd status does not equal Y
- execute the query with the green check mark
- click "Search" (top left)

Depending on your definition of "confirmed" there may be additional criteria such as LOTW.
Hope this helps!

73, Markus HB9BRJ

[w0ls]

Markus...thanks for your input. What I did was went to the "qso archive" page. Set the band filter to "160M" and did a search. Now I have only 160M qso's (1931). Then I went into search parameters and said "lotwqslrcvd != V" and ran it. That gave all qso's that were not matched in lotw (1219) which is my way of knowing whether or not they are confirmed. The problem is this is not telling me anything about which COUNTRIES are not confirmed, only which qso's are not confirmed. I can not find anything that I can search on that will tell me something about the country status, only qso status. Maybe its not possible...

73,
Harry

[w0ls]

Just some clarification. I know there are displays that I can look at under "statistics and awards" that will show me this information. But my goal is to be able to print a list of all qso's with countries not confirmed by band. I can then take that and start going back through each qso to determine which ones would be the best choices to get confirmations from.

Harry

[HB9BRJ]

A partial solution using Log4OM GUI:
- Utilities > Statistics and awards > Country/QSO status grid
- Settings = Show worked only
- Actual View = LOTW status
- filter "Modes" if relevant
- double-click on any cell at the Country/Band crossing point to see the respective QSOs
This shows you all the QSOs per single Country/Band combination. Examine only those countries which do not show received status = V.

In case you want a complete list of all QSOs where the Country/Band combination is not validated in LOTW, I would do it as follows: Access (a copy of) your Log4OM database using an external database tool such as the free Database Browser from http://www.etl-tools.com. Here is the SQL query to generate your list:

Code: Select all

Select t1.* From Log t1
Where Country Not In
  (Select Distinct t2.Country From Log t2
   Where t2.LotwQslRcvd = 'V' And t1.Band = t2.Band)
Order By t1.Country,t1.Band;

73, Markus HB9BRJ

[w0ls]

The SQL database browser was just what I needed. I installed it and ran your script and then exported to excel and it worked great...but...one small problem yet. I noticed in the spreadsheet I had some number of qso's that showed up for band countries that were confirmed. I traced that down to the fact that I have 81 entries in log4om where "QslRcvd" value is a V...81 to be exact. How do I modify the script to ignore these as well? And YES I will try to learn enough of the SCL language so I can take of these things myself. Thanks a lot for all your help.

73,
Harry
W0LS

[HB9BRJ]

Here comes version 2 checking not only LotwQslRcvd but also QslRcvd:

Code: Select all

Select t1.* From Log t1
Where Country Not In
  (Select Distinct t2.Country From Log t2
   Where (t2.LotwQslRcvd = 'V' Or t2.QslRcvd = 'Y') And t1.Band = t2.Band)
Order By t1.Country,t1.Band;

73, Markus HB9BRJ

[w0ls]

Markus the latest script worked perfect! Thank again for taking the time to help me on this problem.

73's
Harry
W0LS

[G4POP]

A perfect example of peer to peer support, thank you

[HB9BRJ]

For a single band, "Harry's list" can be created with Log4OM's excellent Custom search. Here is the 160m example. It must be entered as single-line string:

Code: Select all

Band = '160m' And Country Not In (Select Distinct Country From Log Where (LotwQslRcvd = 'V' Or QslRcvd = 'Y') And Band = '160m')

It will list all 160m QSOs with countries which are neither validated in LOTW nor by QSL card on 160m.
Create one per band and keep it as saved search for future use.

73, Markus HB9BRJ