Update table A from results of searching table B

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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?
 
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
 
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
 
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.
 
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.
 
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
 
Back
Top