John W. Vinson wrote:
On Sun, 25 Apr 2010 22:50:01 -0400, MikeR wrote:

I have tblLog. It's fields a
Call: text
Freq: single
Mode: text
CID: text
QSL_R: text
Credited: T/F

I need the SQL to return all records where 'Credited' is false, 'QSL_R' NULL or
blank, and whose combination of 'Freq' and 'Mode' for a given 'CID' have 'Credited' =
false, but not if that record has a 'mate' whose 'CID', 'Freq/Mode', 'QSL_R' all
match, and 'Credited' is True.

A Query such as

SELECT * FROM tblLog
WHERE [Credited] = False
AND [QSL_R] IS NOT NULL
AND ([Credited] = FALSE AND NOT EXISTS (SELECT [Call] FROM tblLog AS X
WHERE X.Call = tblLog.Call
AND X.Credited = True));


should work. The NOT EXISTS clause may be slow, and could probably be replaced
by a JOIN but I'd need to mock up a table to test it.


Thanks, John.

After playing with your SQL, it's not quite what I want. I think I probably explained
poorly, plus I realized doing anything with 'Freq' was going to be overly complex
because what I'm actually interested in involves a range of 'Freq'. I added a field
called 'Band' (number) to describe a range of 'Freq' (eg a 'Freq' between 13.9 and 15
falls in the 20 'Band').

Speed isn't an issue, it executes fast enough.

I think the field 'Call' is irrelevant. What I really wanted was the country the call
is in, identified by 'CID' ( Country ID). I changed your query to this:

SELECT *
FROM Log
WHERE [Credited] = False
AND [QSL_R] IS NOT NULL and trim(len([qsl_r] + "")) 0
AND ([Credited] = FALSE AND NOT EXISTS (SELECT [CID] FROM Log AS X
WHERE X.CID = Log.CID
AND X.Credited = True))
ORDER BY cid;

which still isn't right. Maybe another example with reasons would help. If the table
is ordered by CID, the records are

Call Freq Mode CID QSL_R Band Credited
1A0KK 14.210 SSB 1A0 Y 20 True
1A0KK 1O.121 CW 1A0 Y 30 True
1A0KK 18.165 SSB 1A0 Y 17 False
1A0KK 21.295 SSB 1A0 Y 15 False
1A0KK 14.160 SSB 1A0 Y 20 False
1A0KK 14.188 SSB 1A0 20 False

The first 2 should not be chosen because 'Credited' is true.
The third and fourth should be chosen because 'Credited' is false and their band and
mode do not match another record with the same 'CID' whose 'Credited' is false.
The fifth should not be chosen because it's 'Band' and 'Mode' and 'CID' do match a
record whose 'Credited' is true.
The sixth would be rejected because 'QSL_R' is not filled.

None of these were selected by our query.

There is also a problem with 'Mode' as it includes ranges also as for example USB,
LSB, SSB, PHO, AM, FM should all be considered the same mode.

It seems to me that I have a table design problem that may make all this very
difficult. I could add another field to do with 'Mode' what I did with 'Freq'.

What's your opinion?

I can send you a .zip of the .mdb that contains this table if it would help. The .zip
is 270K.