 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Importing / Linking / January 2004Pulling info from one excel file to another
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. >Not sure how to look this up in the help menu so... > [quoted text clipped - 15 lines] > >Thanks. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. 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, [quoted text clipped - 37 lines] >Please respond in the newgroup and not by email. >. 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, [quoted text clipped - 66 lines] >> >. 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? [quoted text clipped - 77 lines] >>> >>. -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. 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. jtaylor@groveatlantic.com 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 >Dear John, > [quoted text clipped - 14 lines] >jQQQtaylor@groveQQQatlantic.com >www.groveatlantic.com -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.