Pulling info from one excel file to another

  • Thread starter Thread starter J.D.
  • Start date Start date
J

J.D.

Not sure how to look this up in the help menu so...

I have 2 separate excel files. #1 is a small list where
Column A is filled in with a number. Column B,C,D are
blank. Excel file #2 is a larger list, and column A
consists of all the numbers from column A on #1, plus
more. Plus it has the information in Column B,C,D that I
need to import to #1.

The best way to explain it is, if column A on #1 = column
A on #2, then import text from B,C,D to #1 from #2.

Does that make sense? And how do I go about doing that?

To explain further, #1 is a list missing information.
That information is on the bigger list #2. The link is
that column A on both files is the same.

Thanks.
 
Hi JD,

The simplest way is this:

1) Use File|Get External Data|Link to set up each Excel file as a linked
table (let's call them T1 and T2)

2) Create a query that joins these two tables on column A and returns
the columns B,C and D of T1. The join type should pick up records that
match in both tables (INNER JOIN).

3) Convert this into an update query (select Update from the Query menu)
and set it to update the three fields in T1 to the corresponding values
from T2.
 
Thank you for the advice. I've never used Access before,
so I'm trying to learn. I'm still a little lost. I get
to the point where I have both T1 and T2 open in the
query, and I do the link for Column A, but where to I go
after that? Your #3 confuses me a little.

I'm a little confused on how the drag and drop works.
What exactly do I want to drag into the fields below, and
how do I set it to update the 3 fields?

Thanks.

J.D.
-----Original Message-----
Hi JD,

The simplest way is this:

1) Use File|Get External Data|Link to set up each Excel file as a linked
table (let's call them T1 and T2)

2) Create a query that joins these two tables on column A and returns
the columns B,C and D of T1. The join type should pick up records that
match in both tables (INNER JOIN).

3) Convert this into an update query (select Update from the Query menu)
and set it to update the three fields in T1 to the corresponding values
from T2.

Not sure how to look this up in the help menu so...

I have 2 separate excel files. #1 is a small list where
Column A is filled in with a number. Column B,C,D are
blank. Excel file #2 is a larger list, and column A
consists of all the numbers from column A on #1, plus
more. Plus it has the information in Column B,C,D that I
need to import to #1.

The best way to explain it is, if column A on #1 = column
A on #2, then import text from B,C,D to #1 from #2.

Does that make sense? And how do I go about doing that?

To explain further, #1 is a list missing information.
That information is on the bigger list #2. The link is
that column A on both files is the same.

Thanks.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
When I try to run the query, it says I need at least one
destination field. So what am I not doing here?
Thanks.
J.D.

-----Original Message-----
Thank you for the advice. I've never used Access before,
so I'm trying to learn. I'm still a little lost. I get
to the point where I have both T1 and T2 open in the
query, and I do the link for Column A, but where to I go
after that? Your #3 confuses me a little.

I'm a little confused on how the drag and drop works.
What exactly do I want to drag into the fields below, and
how do I set it to update the 3 fields?

Thanks.

J.D.
-----Original Message-----
Hi JD,

The simplest way is this:

1) Use File|Get External Data|Link to set up each Excel file as a linked
table (let's call them T1 and T2)

2) Create a query that joins these two tables on column A and returns
the columns B,C and D of T1. The join type should pick up records that
match in both tables (INNER JOIN).

3) Convert this into an update query (select Update
Click to expand...
from
the Query menu)
and set it to update the three fields in T1 to the corresponding values
from T2.
Click to expand...
that
I
need to import to #1.

The best way to explain it is, if column A on #1 = column
A on #2, then import text from B,C,D to #1 from #2.

Does that make sense? And how do I go about doing that?

To explain further, #1 is a list missing information.
That information is on the bigger list #2. The link is
that column A on both files is the same.

Thanks.
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
.
Click to expand...
 
You should have a query (in design view) with T1 and T2 visible in the
upper pane, with a join between Column A in each of them.

I don't know what the actual fields are called; use these names instead
of "Column A", "Column B" and so on.

In the lower part of the query, the grid, the first column should have
Field: Column B
Table: T2
Update to: [T1].[Column B]
Subsequent columns in the grid have similar entries for the other fields
you want to update.

You can't drag and drop into the "Update to:" row; you have to type the
table and field names in as in the example above.

What should happen is that the query works through all the records in T1
that have matching records in T2 (i.e. all of them), getting the values
from the fields in the record in T2 and putting them into the field in
the record in T1.



When I try to run the query, it says I need at least one
destination field. So what am I not doing here?
Thanks.
J.D.

-----Original Message-----
Thank you for the advice. I've never used Access before,
so I'm trying to learn. I'm still a little lost. I get
to the point where I have both T1 and T2 open in the
query, and I do the link for Column A, but where to I go
after that? Your #3 confuses me a little.

I'm a little confused on how the drag and drop works.
What exactly do I want to drag into the fields below, and
how do I set it to update the 3 fields?

Thanks.

J.D.
-----Original Message-----
Hi JD,

The simplest way is this:

1) Use File|Get External Data|Link to set up each Excel file as a linked
table (let's call them T1 and T2)

2) Create a query that joins these two tables on column A and returns
the columns B,C and D of T1. The join type should pick up records that
match in both tables (INNER JOIN).

3) Convert this into an update query (select Update
Click to expand...
from
the Query menu)
and set it to update the three fields in T1 to the corresponding values
from T2.
Click to expand...
wrote:

Not sure how to look this up in the help menu so...

I have 2 separate excel files. #1 is a small list where
Column A is filled in with a number. Column B,C,D are
blank. Excel file #2 is a larger list, and column A
consists of all the numbers from column A on #1, plus
more. Plus it has the information in Column B,C,D
Click to expand...
that
I
need to import to #1.

The best way to explain it is, if column A on #1 = column
A on #2, then import text from B,C,D to #1 from #2.

Does that make sense? And how do I go about doing that?

To explain further, #1 is a list missing information.
That information is on the bigger list #2. The link is
that column A on both files is the same.

Thanks.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
.
Click to expand...
Click to expand...
 
Dear John,

Thank you so much for your help with MS Access. With
your instructions, I got the data to merge the 2 excels
files the way I needed it to, and saved us a lot of money
without using our computer people. Appreciate it. I
work for a book publishing company and as a thank you, I
would like to send you a free book. Check out our
website and see if anything interests you, please email
me and I will be happy to send you a copy.

Sincerely,

Judd Taylor
Sales & Marketing
Grove/Atlantic, Inc.
(e-mail address removed)
www.groveatlantic.com
 
Judd,

That's a very kind offer and quite unnecessary! People who answer
questions here are volunteers, and all we expect is the satisfaction of
helping (and hopefully the occasional kind word).

One warning - in the "too late now" category, I'm afraid: including your
real email address in a public group has exposed it to the software that
harvests addresses for spammers. Munging the address - e.g. as below -
reduces the chance of this happening.

John
 
Back
Top