This query should be easy

[JohnH]

I have a simple task to perform that I cannot seem to figure out.

I have two tables:
OR_CUs
OR_CEOs

They share (can be joined on) a field called [CU], which is just the
name of the CU.

All I want to do is get the email address field (EMAIL) from OR_CEOs
and into OR_CUs. Once that's done I will get rid of OR_CEOs. I've
created an empty field in OR_CUs, also called EMAIL.

I've tried UPDATE queries and access always ends up asking me what my
references are (even though they're qualified by the table name). I'm
fairly new to SQL but this seems so elementary. Hah.

Thanks for any help.
John

[Jason Lepack]

In query designer add both tables.
Join the two tables on [CU]
Put the field that you want to update ([OR_CUs].[email]) in the Field
In Update To put [OR_CEOs].[email]

I personally wouldn't use the letters "OR" in a table name because they
look like OR statements in the SQL.

SQL looks like this:
UPDATE OR_CEOs
INNER JOIN OR_CUs ON OR_CEOs.CU = OR_CUs.CU
SET OR_CUs.Email = OR_CEOs.Email;

Cheers,
Jason Lepack

JohnH wrote:

I have a simple task to perform that I cannot seem to figure out.

I have two tables:
OR_CUs
OR_CEOs

They share (can be joined on) a field called [CU], which is just the
name of the CU.

All I want to do is get the email address field (EMAIL) from OR_CEOs
and into OR_CUs. Once that's done I will get rid of OR_CEOs. I've
created an empty field in OR_CUs, also called EMAIL.

I've tried UPDATE queries and access always ends up asking me what my
references are (even though they're qualified by the table name). I'm
fairly new to SQL but this seems so elementary. Hah.

Thanks for any help.
John

[JohnH]

Thanks for your response. I'm having trouble now with case, methinks.
The CU field I'm joining on is all uppercase in one table and lowercase
in the other. I tried adding UCASE( ) for the elements in the ON
clause:

UCASE(OR_CEOs.CU) = UCASE(OR_CUs.CU)

to no avail. Any ideas?
Jason Lepack wrote:

In query designer add both tables.
Join the two tables on [CU]
Put the field that you want to update ([OR_CUs].[email]) in the Field
In Update To put [OR_CEOs].[email]

I personally wouldn't use the letters "OR" in a table name because they
look like OR statements in the SQL.

SQL looks like this:
UPDATE OR_CEOs
INNER JOIN OR_CUs ON OR_CEOs.CU = OR_CUs.CU
SET OR_CUs.Email = OR_CEOs.Email;

Cheers,
Jason Lepack

JohnH wrote:

I have a simple task to perform that I cannot seem to figure out.

I have two tables:
OR_CUs
OR_CEOs

They share (can be joined on) a field called [CU], which is just the
name of the CU.

All I want to do is get the email address field (EMAIL) from OR_CEOs
and into OR_CUs. Once that's done I will get rid of OR_CEOs. I've
created an empty field in OR_CUs, also called EMAIL.

I've tried UPDATE queries and access always ends up asking me what my
references are (even though they're qualified by the table name). I'm
fairly new to SQL but this seems so elementary. Hah.

Thanks for any help.
John

[Jason Lepack]

Are there spaces at the start or end of your names? you could try
Trim( )

Cheers,
Jason Lepack

JohnH wrote:

Thanks for your response. I'm having trouble now with case, methinks.
The CU field I'm joining on is all uppercase in one table and lowercase
in the other. I tried adding UCASE( ) for the elements in the ON
clause:

UCASE(OR_CEOs.CU) = UCASE(OR_CUs.CU)

to no avail. Any ideas?
Jason Lepack wrote:

In query designer add both tables.
Join the two tables on [CU]
Put the field that you want to update ([OR_CUs].[email]) in the Field
In Update To put [OR_CEOs].[email]

I personally wouldn't use the letters "OR" in a table name because they
look like OR statements in the SQL.

SQL looks like this:
UPDATE OR_CEOs
INNER JOIN OR_CUs ON OR_CEOs.CU = OR_CUs.CU
SET OR_CUs.Email = OR_CEOs.Email;

Cheers,
Jason Lepack

JohnH wrote:

I have a simple task to perform that I cannot seem to figure out.
>
I have two tables:
OR_CUs
OR_CEOs
>
They share (can be joined on) a field called [CU], which is just the
name of the CU.
>
All I want to do is get the email address field (EMAIL) from OR_CEOs
and into OR_CUs. Once that's done I will get rid of OR_CEOs. I've
created an empty field in OR_CUs, also called EMAIL.
>
I've tried UPDATE queries and access always ends up asking me what my
references are (even though they're qualified by the table name). I'm
fairly new to SQL but this seems so elementary. Hah.
>
Thanks for any help.
John

[JohnH]

Thanks for all your help Jason, that query was what I needed. I've got
it now. Just need to do a little pruning on one field (some extraneous
characters were gumming up the works) and it's all good from there.

Tip o' the hat to ye,
John
Jason Lepack wrote:

Are there spaces at the start or end of your names? you could try
Trim( )

Cheers,
Jason Lepack

JohnH wrote:

Thanks for your response. I'm having trouble now with case, methinks.
The CU field I'm joining on is all uppercase in one table and lowercase
in the other. I tried adding UCASE( ) for the elements in the ON
clause:

UCASE(OR_CEOs.CU) = UCASE(OR_CUs.CU)

to no avail. Any ideas?
Jason Lepack wrote:

In query designer add both tables.
Join the two tables on [CU]
Put the field that you want to update ([OR_CUs].[email]) in the Field
In Update To put [OR_CEOs].[email]
>
I personally wouldn't use the letters "OR" in a table name because they
look like OR statements in the SQL.
>
SQL looks like this:
UPDATE OR_CEOs
INNER JOIN OR_CUs ON OR_CEOs.CU = OR_CUs.CU
SET OR_CUs.Email = OR_CEOs.Email;
>
Cheers,
Jason Lepack
>
JohnH wrote:
I have a simple task to perform that I cannot seem to figure out.

I have two tables:
OR_CUs
OR_CEOs

They share (can be joined on) a field called [CU], which is just the
name of the CU.

All I want to do is get the email address field (EMAIL) from OR_CEOs
and into OR_CUs. Once that's done I will get rid of OR_CEOs. I've
created an empty field in OR_CUs, also called EMAIL.

I've tried UPDATE queries and access always ends up asking me what my
references are (even though they're qualified by the table name). I'm
fairly new to SQL but this seems so elementary. Hah.

Thanks for any help.
John