Solved

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

Posted on 2011-03-01
8
640 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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
 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

776 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