Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 654
  • Last Modified:

Created a link between two tables from Excel to Access 2010 Professional

I have two tables created in Excel.  One table has 38 fields with student id number as one of the fileds.  I have another table with the same student id number  (labeled OtherID and UICnumber).  I want to compare the two tables where the student id number matches the other id in one table and add the UICnumber into the table with the 38 fields when the student id number matches the otherid.  
0
manch03
Asked:
manch03
  • 3
  • 3
  • 2
1 Solution
 
manch03Author Commented:
That is Create a link...
I am a newbie to this so step by step will help.  I looked at the Microsoft help and cannot get it to work.
0
 
als315Commented:
1. Import Excel tables into Access (From External data tab)
2. Check Id's fields in both imported tables. (Open table in design mode). Type and length of both fields should be exactly same (Name may be different)
3. Create query with master (add both tables). Join ID fields (select field in one table and move it to same field on second table. Add necessary fieds for output (ID, UICnumber)
4. Run query
0
 
als315Commented:
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
manch03Author Commented:
I was able to do this successfully with two files yesterday.  I have several files that I need to link to the one table uic/student file.  I cannot get it to work again.  My file gets created but I have no data in the newly created query.  Very strange.  I have tried closing everything and starting from a blank database.  Still no luck.  
0
 
als315Commented:
Can you upload you files and DB (without sensitive information)?
0
 
Don ThomsonCommented:
Rather than import the files into the table - link them to the excel spreadsheet- Then do an unmatched query between the two tables on Whatever Criteria you need to match on. If you're new to access then just user the built in query stuff - One of them uner new should be unmatched  - Chhose the table "A" first vs table "B"  then do another chosing table B first then table A
0
 
manch03Author Commented:
Can you give more details?  We understand how to do this, but how do we link the two spreadsheets without importing into excel?
0
 
Don ThomsonCommented:
Click on Tables - Then New - Link change type to XLS  - Go find your XLS speadsheet and open then it will take you through the steps

MAke sure that all fileds have a header row that is unique in the spreadsheets
If you select the Letter at the head of a column - right click and format - And force a column to be Text, Currency, whatever - It's a lot easier to import - Make sure to save and close the spreadsheet before trying to import.

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now