Update table A from results of searching table B

M

MeJayne

I have two tables, A & B. I want to update an empty column in Table A depending on a search result of Table B. My problem is, the text in Table A that I'm searching for in Table B isn't an exact match. In Table B, this text is deep within a column of other text
Anyone have any ideas of a query I could use for this?
 
E

Edmund

You may want to show some examples here in order to help you ou

Edmun
MCP - Access and SQL Serve
----- MeJayne wrote: ----

I have two tables, A & B. I want to update an empty column in Table A depending on a search result of Table B. My problem is, the text in Table A that I'm searching for in Table B isn't an exact match. In Table B, this text is deep within a column of other text
Anyone have any ideas of a query I could use for this?
 
M

MeJayne

TABLE

COLUMN A COLUMN
x 97668
60793
90764
x 142964


TABLE

COLUMN A COLUMN

yellow word 976689 wor
blue names 12345654 name
green words 321321 word
orange names 1429642 name

In TABLE A, I copy the text in Column B, then search for that text in TABLE B, COLUMN B
If it is a match, then I put an "x" in TABLE A, COLUMN A. If it is not a match, then the field is left blank. I need a formula that will conduct the search for TABLE A, COLUMN B in TABLE B, COLUMN B, and update with the "x" when it finds the text it's looking for in TABLE A, COLUMN A
 
E

Edmund

To the best of my knowledge, i don't think it can be done by using Access Query
I can have a simple VBA code to get it done, though
Let me know if you want i

Edmun
MCP - Access and SQ

----- MeJayne wrote: ----

TABLE

COLUMN A COLUMN
x 97668
60793
90764
x 142964



TABLE

COLUMN A COLUMN

yellow word 976689 wor
blue names 12345654 name
green words 321321 word
orange names 1429642 name


In TABLE A, I copy the text in Column B, then search for that text in TABLE B, COLUMN B
If it is a match, then I put an "x" in TABLE A, COLUMN A. If it is not a match, then the field is left blank. I need a formula that will conduct the search for TABLE A, COLUMN B in TABLE B, COLUMN B, and update with the "x" when it finds the text it's looking for in TABLE A, COLUMN A
 
E

Edmund

Yes, it is in Access
It is in FORM not QUER
...

Edmun
MCP - Access and SQL Serve

----- MeJayne wrote: ----

Thanks, but I have to have it in Access. I appreciate your help, however.
 
J

Jeff Boyce

Are you saying that you want the same text in two tables? This violates the
principle (or is it a rule? ?a convention?) of avoiding redundancy in a
relational database. If you'll describe what you are trying to accomplish,
rather than how, the 'group readers may be able to offer alternative
approaches.
 
J

John Spencer (MVP)

Not sure this will be updateable, but you might try

UPDATE TableA
Set ColumnA = "X"
WHERE ColumnB IN
(SELECT T.ColumnB
FROM TableB as B INNER JOIN TableA as T
 
Top