Solved

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

Posted on 2011-03-01
8
635 Views
Last Modified: 2012-05-11
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
Comment
Question by:manch03
  • 3
  • 3
  • 2
8 Comments
 

Author Comment

by:manch03
ID: 35007402
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
 
LVL 39

Expert Comment

by:als315
ID: 35007487
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
 
LVL 39

Expert Comment

by:als315
ID: 35007513
0
 

Author Comment

by:manch03
ID: 35008158
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 39

Expert Comment

by:als315
ID: 35008576
Can you upload you files and DB (without sensitive information)?
0
 
LVL 14

Expert Comment

by:Don Thomson
ID: 35009216
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
 

Author Comment

by:manch03
ID: 35009355
Can you give more details?  We understand how to do this, but how do we link the two spreadsheets without importing into excel?
0
 
LVL 14

Accepted Solution

by:
Don Thomson earned 500 total points
ID: 35009428
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now