Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 652
  • 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
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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