Update question for a query

I have two tables, A-Table and B-Table. A-Table has Names(Text), Numbers(Num). B-Table has Names(Text), Numbers(Num).

I want to replace all A-Table with Names for Names = Numbers from B-Table for A-Table Names = "Null" or blank

REMEMBER: A-Table has the Numbers, but not the names, B-Table has both names and numbers AND I ONLY WANT the names that are blank (null)

Is there a way I can do this? Thanks all

What I'm understanding is that you want to put a number in for a name in
table A if the name in table A is Null (empty)? But, if you want to join the
two tables on the name field from table A, the join won't work if name is
empty in table A.

Perhaps you can show a few examples of data and what the result should be to
help us better understand.

--

Ken Snell
MS ACCESS MVP

"bladelock" wrote in message
...
I have two tables, A-Table and B-Table. A-Table has Names(Text),
Numbers(Num). B-Table has Names(Text), Numbers(Num).

I want to replace all A-Table with Names for Names = Numbers from B-Table
for A-Table Names = "Null" or blank

REMEMBER: A-Table has the Numbers, but not the names, B-Table has both
names and numbers AND I ONLY WANT the names that are blank (null)

Is there a way I can do this? Thanks all

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

UPDATE TableA As A LEFT JOIN TableB As B
ON A.Numbers = B.Numbers
SET A.Name = B.Name
WHERE A.Name Is Null

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQRF99IechKqOuFEgEQIXaQCg7WhDeYb7B23RJwqvi18yoH t1JV0AnRw4
wg1Y7Hnj7LRgujOx3fJlIpw9
=7xmj
-----END PGP SIGNATURE-----


bladelock wrote:

I have two tables, A-Table and B-Table. A-Table has Names(Text), Numbers(Num). B-Table has Names(Text), Numbers(Num).

I want to replace all A-Table with Names for Names = Numbers from B-Table for A-Table Names = "Null" or blank

REMEMBER: A-Table has the Numbers, but not the names, B-Table has both names and numbers AND I ONLY WANT the names that are blank (null)

Is there a way I can do this? Thanks all

I think I might understand your problem.

UPDATE ATable INNER JOIN BTable
On ATable.Numbers = BTable.Numbers
SET ATable.Names = BTable.Names
WHERE ATable.Names is Null Or ATable.Names = ""

bladelock wrote:

I have two tables, A-Table and B-Table. A-Table has Names(Text), Numbers(Num). B-Table has Names(Text), Numbers(Num).

I want to replace all A-Table with Names for Names = Numbers from B-Table for A-Table Names = "Null" or blank

REMEMBER: A-Table has the Numbers, but not the names, B-Table has both names and numbers AND I ONLY WANT the names that are blank (null)

Is there a way I can do this? Thanks all

Guess I need more coffee in the evenings.....

!!!


--

Ken Snell
MS ACCESS MVP

"John Spencer (MVP)" wrote in message
...
I think I might understand your problem.

No, just more relaxation, so your psychic abilities can kick in and interpret
the OP's meaning by enfolding the entire posting and 'grokking' it. See
"Stranger in a Strange Land" by R.A. Heinlein for the reference.

Ken Snell wrote:

Guess I need more coffee in the evenings.....

!!!

--

Ken Snell
MS ACCESS MVP

"John Spencer (MVP)" wrote in message
...
I think I might understand your problem.

"John Spencer (MVP)" wrote in message
...
No, just more relaxation, so your psychic abilities can kick in and
interpret
the OP's meaning by enfolding the entire posting and 'grokking' it. See
"Stranger in a Strange Land" by R.A. Heinlein for the reference.


I have that one! g
--

Ken Snell
MS ACCESS MVP

Thanks again

"John Spencer (MVP)" wrote:

I think I might understand your problem.

UPDATE ATable INNER JOIN BTable
On ATable.Numbers = BTable.Numbers
SET ATable.Names = BTable.Names
WHERE ATable.Names is Null Or ATable.Names = ""

bladelock wrote:

I have two tables, A-Table and B-Table. A-Table has Names(Text), Numbers(Num). B-Table has Names(Text), Numbers(Num).

I want to replace all A-Table with Names for Names = Numbers from B-Table for A-Table Names = "Null" or blank

REMEMBER: A-Table has the Numbers, but not the names, B-Table has both names and numbers AND I ONLY WANT the names that are blank (null)

Is there a way I can do this? Thanks all

When I use this example; I get a popup for the parameter ? Here is my sequel:

UPDATE NB_200912A INNER JOIN NB_200912MAIL ON NB_200912A.MEMID =
NB_200912MAIL.MEMID SET NB_200912MAIL.Adr = ADDRESS.NB_200912A
WHERE (((NB_200912MAIL.Adr) Is Null Or (NB_200912MAIL.Adr)=" "));

What am I missing?

"John Spencer (MVP)" wrote:

I think I might understand your problem.

UPDATE ATable INNER JOIN BTable
On ATable.Numbers = BTable.Numbers
SET ATable.Names = BTable.Names
WHERE ATable.Names is Null Or ATable.Names = ""

bladelock wrote:

I have two tables, A-Table and B-Table. A-Table has Names(Text), Numbers(Num). B-Table has Names(Text), Numbers(Num).

I want to replace all A-Table with Names for Names = Numbers from B-Table for A-Table Names = "Null" or blank

REMEMBER: A-Table has the Numbers, but not the names, B-Table has both names and numbers AND I ONLY WANT the names that are blank (null)

Is there a way I can do this? Thanks all

You seem to have flipped the table name and field name in the set clause.
Also put brackets around the table name and field name on the right side

Change the following
SET NB_200912MAIL.Adr = ADDRESS.NB_200912A
to
SET NB_200912MAIL.Adr = [NB_200912A].[ADDRESS]

UPDATE NB_200912A INNER JOIN NB_200912MAIL
ON NB_200912A.MEMID = NB_200912MAIL.MEMID
SET NB_200912MAIL.Adr = [NB_200912A].[ADDRESS]
WHERE (((NB_200912MAIL.Adr) Is Null Or (NB_200912MAIL.Adr)=" "));


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Tloliver79 wrote:
When I use this example; I get a popup for the parameter ? Here is my sequel:

UPDATE NB_200912A INNER JOIN NB_200912MAIL ON NB_200912A.MEMID =
NB_200912MAIL.MEMID SET NB_200912MAIL.Adr = ADDRESS.NB_200912A
WHERE (((NB_200912MAIL.Adr) Is Null Or (NB_200912MAIL.Adr)=" "));

What am I missing?

"John Spencer (MVP)" wrote:

I think I might understand your problem.

UPDATE ATable INNER JOIN BTable
On ATable.Numbers = BTable.Numbers
SET ATable.Names = BTable.Names
WHERE ATable.Names is Null Or ATable.Names = ""

bladelock wrote:
I have two tables, A-Table and B-Table. A-Table has Names(Text), Numbers(Num). B-Table has Names(Text), Numbers(Num).

I want to replace all A-Table with Names for Names = Numbers from B-Table for A-Table Names = "Null" or blank

REMEMBER: A-Table has the Numbers, but not the names, B-Table has both names and numbers AND I ONLY WANT the names that are blank (null)

Is there a way I can do this? Thanks all