Query keyword from table, need it to tag result with column keyword was in

Keyword query, need it to create field in data table with field keyword was found in

I've got 2 tables, one which has records in it, and I have a second table that has is only a lookup table and not conforming to good database practices with keywords in it. Each field on the lookup table is a category and I need the query to add a field to the records table if it doesn't exist or fill that field for that record telling which field the keyword match in the lookup table was from.
Here's an example of what I have and *what I want it to do*:


Reads fields from table 1 checking for words from a specific field in table 2, when finding a word in any record from that field of table 2, puts the record in new table, I would like it to then *populate a field in the data table with the name of the field from the lookup table where the word was found*

*I would like it to do this for every field in the lookup table until all records are sorted, and if it runs out of fields and there are still records left to be sorted, label them as Other in the field dedicated to which field the keyword source was in*

Can anyone help? I've got it working as far as taking records and putting them in a table based on keywords in the lookup table but I don't know how to make it do more than one field at the same time, and if I did that then I would just end up with all records in a different order unless I knew how to fill a field with the label noting which field the word was found in.

First up welcome to the forum.

I'm not entirely sure I understand exactly what it is you are trying to do, but I do suspect that it is braking at least one rule of good DB normalisation.

Technically Access does not have columns (this is an Excel concept), it has Records (these would be Rows in Excel terms) and Fields (I suspect this is what you are thinking of as columns). If, as I suspect, you are approaching this from an Excel point of view, the hardest thing you are going to have to do is to forget about thinking of the problem in Excel terms, you need a whole new pattern of thinking when dealing with relational DB like Access.

I like the buckaroo bonzai reference. Yeah, I'm probably breaking several good DB keeping rules, mainly because I'm trying to use the fields as records and I intentionally need to go around some restrictions because this db is only an extension of an SAP/oracle db, and I've gotten it to work with searching one field at a time, but I do not know how to make a query add a field that did not previously exist, or if the field exists populate it with information about which field the search term was found in.

This will not be translated back to the original database, it is only a lookup table that the end users will not be touching and if you want me to fix it, I would happily comply if I can find out how to make it search every record and create a new field in the data table or populate that same new field with the first field in the record.

Is this the most active Access board out here?

You can use an Append query to append records into a table. However you don't want to be dynamically adding new fields to a table that already holds data. You should have your Table structure pretty well set in stone before you start shifting data around.

Perhaps I'm being obtuse but I'm still not sure I follow exactly what it is you are trying to achieve.

Could you perhaps outline step by step what you currently have and what you want to end up with.

I currently have a record table, a lookup table, and an empty table. The query compares the records of the record table to the words in a field of the lookup table, if the record contains the words, it copies the record to the empty table. Because the empty table is an empty copy of the records table I was wanting the query to add a field if the field did not already exist. That's not the important part of my question. The important part of my question is how do I append a specific field on my record with the name of the field from the lookup table where the query found the words.

Say I want to separate the pumps from the records.
As per my example tables above, the query currently uses the field pumps from the lookup table (table2), checks the fields description1 and description2 in the record table,
finds a match with the second record in the pump field from the lookup table *pump* that matches record 2 field description1 in the record table. Then it copies the complete record to the blank table. I have it successfully doing that so far.

Upon successfully doing that, I want it to append the record in the new table. The important part I want is for the record to have the word "Pumps" appended to the records found via the query.

(BTW, is there any way to make a form perform the query whenever the form is opened?)

Any chance you can post a sample of the data you are working with (and by all means strip out anything sensitive).

Yes you can use a Form's On Load event to run either a query or an SQL statement equivalent to that query.

Here's a little question to ponder; If we can write a query to achieves what you want (and I suspect we probably can), why would we want to store that result, when we could simply run the query at any time we required that information?

Thank you for the info of editing the forms code adding an on load function.
The reason we would need to store the result is that sometimes it may not be accurate, and may need to be re-categorized. Categorizing based on keywords can sometimes include records that aren't what I want and I need to be able to update each record so if it is mis-categorized it doesn't continuously come up under the wrong category. There needs to be a field for that data to be stored in so we don't have to change any of the other fields in that record. The record table is from a SAP dump and is input by hundreds of different people with no real control over content. This initial query I am working on would only need to be run once every few months, after that another query I will be creating will only show the table where everything has already been categorized. These keywords are from a list table created by me to separate items so that a general idea over how many of each job is in the works. Being able to re-categorize ones that people find are mis-labeled would allow the number to be more accurate and that is why I need the initial query to add a field if it doesn't already exist, and populate that field based on which field the word match came from.
I am attaching a sample, with 2 queries that do what I have been talking about, searching a records table for a match with keywords from a lookup table. I am interested in using the V1 query because it does not have to include more columns than are currently in the records table, even though I will have to add a column for the info concerning which field the match was found from.
I know how to make an update query, or a new table query, but I need to know how to make the query add a field if the field is not there, and how to update that specific field with which fields name the keyword match was in. At this time because the keyword only exists within 1 field, that match will always show up with the same name, but I hope to expand the query to include every field from the lookup table.

Attachment included:

Similar thread using IIf statements here:
http://www.access-programmers.co.uk/forums/showthread.php?t=195014

Ok, found out how to tag each record using a select query, just need to change it into an update query. If you want to see my sql to understand what i'm doing, here it is:
Of course this isn't as accurate as I want it to be, and I will be figuring out other bits of what I need as I go along, but this is an example of part of what I needed.
That sufficiently describes the part I was asking about, tagging the records, and once I change that into an update table query it will tag each record with the category I want. Now I just need to figure out how to make an update table query add a field if it doesn't already exist. I would also like to be able to populate using a field's name rather than my own label. Finally I would like to be able to make it loop through the whole table rather than just one field. I guess I will have to get this refined to the point to where everyone can understand it and my example has everything in it, then I will be able to ask correctly.