Add data to specific fields within existing records in a table from another table.

[Taz]

Hello, I would like to use a query or other method to add data from one
table to specific fields in existing records in another table. I tried to
use an append query but this will only add new records to the other table,
not add data to fields in existing records.

Example: TableA and TableB

Table A has fields for "Customer ID", "Address 1", "Address 2" and "Email"
All the fields except "Email" contain data.

TableB has two fields "Customer ID" and "Email"-both contain data.

I need to add the data "email" from each record in TableB to the matching
field in TableA based on "Customer ID" which is the primary key in each
table.

Please help. Thanks.

 

[MGFoster]

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

You may wish to re-think this design because it is against the rules
of relational database design to have redundant data in many tables.
IOW, the email address should only be in one table.

If I were you I'd keep the designs for the tables as you have them,
except remove the "Email" column from TableA. Then link/relate the
two tables as a one (TableA) to many relationship (TableB) on the
CustomerID column. When you want to "see" the email for a specific
customer you'd have a SELECT query like this:

SELECT A.CustomerID, B.Email
FROM TableA As A INNER JOIN TableB As B
ON A.CustomerID = B.CustomerID
WHERE A.CustomerID = xxxxxx

Substitute the real customer ID for the xxxxxx.

=====

To copy data from one table to another table's existing records use
the UPDATE command (JET [Access] SQL syntax):

UPDATE TableA AS A INNER JOIN TableB As B ON A.CustomerID =
B.CustomerID
SET B.Email = A.Email

Rgds,

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

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

iQA/AwUBQCGNa4echKqOuFEgEQLb2wCdGvBB1TCpTFB6Ud5RKvk+/HN1RW4An34Y
9S13LHL1CqzWx+2hjnoW1X5A
=7zjd
-----END PGP SIGNATURE-----

 

[Taz]

Thank you for the response. I should have mentioned that the TableB
containing the "email address" is a temporary table that was downloaded from
the server-once the "email address" records are added to TableA, TableB will
be deleted. Is there a way to do this using a query or do I have to use the
SQL command? If I can do it with the SQL command, could you give me a little
more info as to how to do it or point me to an knowledgebase article. I am
not experienced with using SQL commands.

Thanks for your help.

 

[John Vinson]

Is there a way to do this using a query or do I have to use the
SQL command? If I can do it with the SQL command, could you give me a little
more info as to how to do it or point me to an knowledgebase article.

All Queries are written in SQL. The query grid is simply a tool to
build SQL strings!

If the poster who answered your question posted some SQL, you can
create a new Query, go to SQL view, and copy and paste the SQL text
into the window. Change the table and field names to match yours if
need be; you can then go back to the query design window for a more
familiar view.

 

[Taz]

Thanks for the input-could someone post some SQL code on how to do
this-thanks.

 

[John Vinson]

Thanks for the input-could someone post some SQL code on how to do
this-thanks.

UPDATE TableA
INNER JOIN TableB
ON TableA.[Customer ID] = TableB.[Customer ID]
SET TableA.EMail = TableB.EMail;

or, in the grid, create a Query by adding TableA and TableB to the
query; join them by Customer ID if they aren't joined automatically.
Change the query to an update query by selecting Update Query from the
query type icon dropdown or the Query menu option. On the Update To
line under TableA.EMail type

[TableB].

The brackets are required; otherwise you'll just update all the email
addresses to "TableB.EMail"!