Adding fields to existing table from another table

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

Guest

I have two tables. One has nearly all the information I want but I need
addresses that are in another table. There is matching field in both tables
that holds an ID number. How can I input the addresses to the corresponding
record? I thought I could do a make-table query but I realized that those
records that may not have a corresponding ID in the second table will be left
out entirely from the first table b/c it will only pull records if the ID has
found its match. I need all the informtation in the first table and the
corresponding addresses in the second table. Is there a way to add the
addresses from the second table to the first table only if it has a
corresponding ID. Please advise.
 
This will update the fields in the first table only when the ID in the first
table matches the ID in the second table:

UPDATE FirstTable INNER JOIN SecondTable ON FirstTable.ID = SecondTable.ID
SET FirstTable.Addesss1 = SecondTable.Address1, FirstTable.Addesss1 =
SecondTable.Address1;
 
You had the right idea with the Make Table query, you just need to change
your joins. Right click on the join line between tables and pick the choice
where you include all records from the first table and only the records from
the second table where they are equal.

Hope this helps.
 
I have two tables. One has nearly all the information I want but I need
addresses that are in another table. There is matching field in both tables
that holds an ID number. How can I input the addresses to the corresponding
record? I thought I could do a make-table query but I realized that those
records that may not have a corresponding ID in the second table will be left
out entirely from the first table b/c it will only pull records if the ID has
found its match. I need all the informtation in the first table and the
corresponding addresses in the second table. Is there a way to add the
addresses from the second table to the first table only if it has a
corresponding ID. Please advise.
Click to expand...

You really do NOT need to, nor should you, store the address in two
different tables. If you want to see the address information in Table2
in conjunction with the other information in Table1, all that you need
to do is use a Query joining the two tables on the ID field. Select
the join line in the query designer and choose Option 2 - 'show all
records in Table1 and matching records in Table2' - and you will get
just that: addresses where they exist, and NULL fields where they
don't.

If you will be discarding Table2 and want to permanently move the
addresses into Table1, you can make this Left Join query into an
UPDATE query to update the (perhaps newly created) address field or
fields in Table1; a MakeTable query would be going from bad to worse,
since it would be redundant with BOTH existing tables.

John W. Vinson [MVP]
 
Back
Top