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.  
manch03Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Don ThomsonConnect With a Mentor Commented:
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
als315Commented:
0
 
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
All Courses

From novice to tech pro — start learning today.