Searching across records

I am stumped with a particular problem - wonder if anyone has any ideas.

Consider this table of tblPubAuthors

PubID, txtAuthors
001, Tom
001, Jerry
002, Tom
003, Jerry
004, Bob
005, Tom
005, Jerry
005, Bob
006, Bob
006, Jerry

If I search for "Tom OR Jerry" I will get PubID 001, 002, 003, 005 and 006. If I search on "Tom AND Jerry", I will get zero results because no txtAuthors field contains both Tom and Jerry such as:

PubID, txtAuthors
001, Tom Jerry
005, Tom Jerry Bob

so, I don't know how to find just 001 and 005 which are the only publications for which txtAuthor lists both Tom and Jerry (I don't care if there are other authors, the minimum would be Tom AND Jerry).

Here's a thought:

Set recset = db.recordset("Select PubID, txtAuthor from tblPubAuthors where txtAuthors = 'Tom' or txtAuthors = 'Jerry'")

which will result in:

PubID, txtAuthors
001, Tom
001, Jerry
002, Tom
003, Jerry
005, Tom
005, Jerry
006, Jerry

then:

Set recset2 = db.recordset("Select PubID from recset Where count(PubID) > 1 Group on PubID")

(pardon the syntax if I have made a mistake but I hope you see where I am headed)

Might this work? If I have the count() function correct, since count of 002, 003 and 006 are 1 which is not >1, then recset2.PubID will contain 001 and 005.

Any takers? If this seems to be able to work, is there anyway I could combine the two selects?

TIA,

OK got it now and it is a very elegant solution. I need it to work in Access 97 at work and because I have Access 2000 at home, I will have to wait until tomorrow to test it.

The SQL is:

SELECT PubID
FROM tblPubAuthors
WHERE txtAuthors = 'Tom' OR txtAuthors = 'Jerry'
GROUP BY PubID
HAVING Count(PubID) >= 2

I will modify this code to use "LCase(txtAuthors) Like '*tom*'" in place of "txtAuthors = 'Tom'" to allow for some mispellings but the basics are there and within one SQL query, all the work is done.

Hope this helps someone else as much as it helps me,