Add two Columns to a table from another table

G

GregB

It has been a couple of months since I have worked with access and I can't
remeber the right way to put the critera. I want to add two columns from
table A to table B where the machine name is equal to each other.

I know I have to use an append query
I just can't figuer out how to set the critera to place the correct data in
the right place. Both tables have been imported and both have a column called
machine name. I want to add two columns from table A to table B where the
machine names are the same.

Please help me out.

Thanks very much,
Greg
 
J

Jeff Boyce

It isn't clear whether you are adding new columns to a second table, or
adding the data from one table to another.

In either case, it sounds like you'd end up with two copies of the same
data. If so, which table's data is the correct data? I ask because having
redundant data (same fact stored more than one place) can suffer
synchronization problems (a change to one that doesn't get reflected in the
other).

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/
 
S

Sprinks

Greg,

An Append query adds new *records* to a table. Since you want to match
records from the two tables having the same machine name, you need an Update
query.

First, if you haven't done so already, add fields to TableB of the same
field type as in Table A. An Update query changes the value of pre-existing
fields but won't add a new field. The query then updates the value of each
Table B column from the other table's corresponding value, joined by the
MachineName:

UPDATE TableA INNER JOIN TableB ON TableA.ID = TableB.ID SET TableB.Column1
= [TableA].[Column1], TableB.Column2 = [TableA].[Column2];

Hope that helps.
Sprinks
 
G

GregB

ok, let me try to clarify

I have two reports that I generated in Excel. I imported them into two
tables in Access

In table A there is Machine Name, company, location
In table B there is Machine Name, user, specs

I want to add company and location to table B Where the machine names are
the same

And then I will export the file backinto an excel file

Does that clarify things?

Thanks for the responce
 
G

GregB

Ok thanks, I forgot I could set a relation in a query and was to caught up on
trying to come up with an expression in the critera field

THanks a lot

Sprinks said:
Greg,

An Append query adds new *records* to a table. Since you want to match
records from the two tables having the same machine name, you need an Update
query.

First, if you haven't done so already, add fields to TableB of the same
field type as in Table A. An Update query changes the value of pre-existing
fields but won't add a new field. The query then updates the value of each
Table B column from the other table's corresponding value, joined by the
MachineName:

UPDATE TableA INNER JOIN TableB ON TableA.ID = TableB.ID SET TableB.Column1
= [TableA].[Column1], TableB.Column2 = [TableA].[Column2];

Hope that helps.
Sprinks

GregB said:
It has been a couple of months since I have worked with access and I can't
remeber the right way to put the critera. I want to add two columns from
table A to table B where the machine name is equal to each other.

I know I have to use an append query
I just can't figuer out how to set the critera to place the correct data in
the right place. Both tables have been imported and both have a column called
machine name. I want to add two columns from table A to table B where the
machine names are the same.

Please help me out.

Thanks very much,
Greg
Click to expand...
Click to expand...
 
J

Jeff Boyce

Unlike Excel, you don't need to "add columns to tables" to get a report in
Access. It appears that "MachineName" is a field in common between your two
tables. If so, use a query to join the two tables, select the fields you
want to see, and "export" the query to Excel. It is actually the data that
gets exported.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Top