Eljefegeneo
Still trying to learn
- Local time
- Yesterday, 23:14
- Joined
- Jan 10, 2011
- Messages
- 902
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.
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?
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.
Code:
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] & "*"));
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?