Is this possible?

[Eljefegeneo]

Before I start on my quest to figure this latest conundrum out, I would like to know if it is even possible with Access, either by a query or by a report.
I have three tables: tblMain, tblNotes and tblContacts.
tblMain:
ClientID (PK)
Notes (memo field)

tblNotes:
NoteID (PK)
ClientID (number)
DateOfEntry (date/time)
Notes (text)

tblContacts:
ContactID (PK)
ClientID (number)
DateOfContact (Date/time)
Notes (text)

The relationship between tblNotes and tblContacts with tblMain is by the ClientID.

I would like to be able to search by one to five different key words so that if the keyword appeared on any of the three tables for a specific ClientID, a record would appear. I have figured out how to get each one individually with a pop-up form to select the key words and the query.

  SELECT tblNotes.NotesID, tblNotes.ClientID, tblNotes.DateOfEntry, tblNotes.UserName, tblNotes.Notes
  FROM tblNotes
  WHERE (((tblNotes.ClientID) Like "*") AND ((tblNotes.Notes) Like "*" & [Forms]![frmNotesSearchAll]![TS1] & "*")) OR (((tblNotes.Notes) Like "*" & [Forms]![frmNotesSearchAll]![TS2] & "*")) OR (((tblNotes.Notes) Like "*" & [Forms]![frmNotesSearchAll]![TS3] & "*")) OR (((tblNotes.Notes) Like "*" & [Forms]![frmNotesSearchAll]![TS4] & "*")) OR (((tblNotes.Notes) Like "*" & [Forms]![frmNotesSearchAll]![TS5] & "*"));

I have two similar queries for tblMain and tblContacts.

My end result would be to determine if (any of) the keyword(s) appeared in any of the queries together, that is, if it appeared in at least one of the queries. So if I were searching for the word "money" and it appeared in any one of the three tables, then I would know that client record by the ClientID. I would then hope to be able to construct a report based on this. So if in the records for ClientID = 999 the keyword "money" appeared in at least one of the three tables, I would flag that ClientID. I think that I could then make a combo report with the grouping by ClientID and sub reports from the three separate queries.

Is this even possible or do I have to start over and combine all the notes fields in one table?

 

[JHB]

You could use a Union query.

 

[Eljefegeneo]

Thank you. Figured out the Union query, got 94 results with one duplicate due to keyword appearing in more than one query in the Union of three queries. Then made another query to select the distinct value.

So far so good. Now to construct the report. Hopefully I will not have to ask for more help.

 

[Eljefegeneo]

JHB, wanted to thank you again. Figured it out, works great. Just the right nudge from you did it. Sometimes I do secretly wish someone would just write the code for me, when when I get the right nudge and figure it out, I have learned a lot.

 

[JHB]

You're welcome, good luck.