When importing an Access table into Excel, a look-up column is showing all values as numbers

Question

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!

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