2

I have a basic Access to Excel question that has me frustrated.

I have two Access 2010 data tables. One is a list of managers. The primary key is a manager ID (which is an autonumber because managers can have the same name), and each row also has manager name, manager email, etc.

The second data table is a list of departments. The primary key for each row is a unique department code, and the foreign key is a manager ID (autonumber). I used the Look-up Wizard to create this connection. However, Access does not show the manager ID in the foreign key location. It shows Manager Name like I requested when I used the Look-up Wizard.

Now I am trying to import the second table (departments) into Excel 2010. I clicked import from Access, chose the Department table, and everything popped into Excel. BUT, the Manager Name column is showing Manager ID instead. So I have a list of numbers instead of names.

How can I make Excel show what I see in Access?

Thanks!

user3651997
  • 33
  • 2
  • 5

1 Answers1

1

The underlying data for the table is the manager ID.

You will need to create a query that will display the manager name and then import that information into Excel. In this way, the "underlying" data will be the manager's name.

SELECT *.Departments, ManagerName.Manager FROM Departments INNER JOIN Manager ON ManagerID.Departments = ManagerID.Manager

Obviously this SQL statement won't work because I don't know the table constructs but the concept contained with the statement is valid.

wbeard52
  • 3,417
  • 3
  • 28
  • 40
  • I have written a query like that in Access, and it does work. However, it doesn't seem that you can link an Access Query to Excel. Like you said, I can import the information from the query into Excel. But it isn't live -- if I change the Access database the query info won't change – user3651997 Jun 03 '14 at 14:56
  • Never mind, I have fixed that issue. Now I can see queries when I choose to import into Excel. Thanks for the help – user3651997 Jun 03 '14 at 16:28