Query based on two unrelated tables

[jjmclell]

I have a table (Table1) that has a text field where users can enter free-form text. Some of the info that they'll invariably enter contains words that match records in an unrelated table (Table2).



Is there anyway I can query records in Table1 where there is text in the aforementioned text field that matches records in Table2?

For example, if the field in Table1 contains a record "Bruce gave Carol $25", and a field in Table2 contains "Bruce" as one of its records, can I query out that record in Table1 based on the records in Table2?

Furthermore, if "Bruce", "John", and "Mary" all belonged to a group called "Sales" in a related table, could I query Table1 to pull out records where any name from the "Sales" group is mentioned in the text field in Table1?

[ItsMe]

You want to use Keys to create a relationship between your tables. You will want a Primary Keys in your tables. Also, include Foreign Keys to create a relationship from one table to another.
https://www.youtube.com/watch?v=_aN-8kszIdA

[jjmclell]

I can't have a relationship between the tables. It would never align. It's like having a table where in any given record someone might mention multiple people's names along with a slew of other text (like a description field) and then trying to have a one-to-many relationship with a table of names.

What I want to do be able to do is go thru records in the table with the free-form text field looking for records where the free-form text field is like *Bruce* or *Mary* or whatever, but have Access check for any name that is in an unrelated table of nothing but names.

[ItsMe]

Perhaps what you want to do is employ a search form technique. Maybe this will provide some ideas.
http://allenbrowne.com/ser-62.html

[jjmclell]

I figured it out. I first created a query that showed all the records in Table1 x all the records in Table2. I then queried that query to show only records where data from [FieldX] (originally from Table1) contains the name in the corresponding record data from [FieldY] - in the criteria cell of the queries design view for [FieldX]: [FieldX] = "*" & [FieldY] & "*".

[ItsMe]

Glad to hear you found a solution and thanks for posting your solution here.

[June7]

That is a Cartesian relation of two tables.

[orange]

jjmclell,

Just for reference you may want to work through this tutorial that will help you with relational database design concepts. It has a structured methodology and it has a solution, but you have to work through it.

You can work with unrelated tables, but you don't need a relational database to do that.

Good luck with your project.