Looking up a value in another datatable when creating a report viewer report

I have a local report that I created in my application. It displays the contents of a table in my access database called "History". One of the columns is "ID" and it refers to the primary key in another table "Employees".

My report shows the employee's ID when it is refreshed but of course I want it to display the employee's name instead.

In short, I need to be abl开发者_StackOverflow社区e to perform a lookup on another table in my rldc file. How can I go about that?

I assume you fetch data from the database through a dataadapter and a typed dataset. something like this?

        TestDataSetTableAdapters.CategoryTableAdapter ca = new TestDataSetTableAdapters.CategoryTableAdapter();
        this.ds1 = new TestDataSet();
        ca.Fill(this.ds1.Category);

Then you go to the dataset and modify the query in your tableadapter to something like this

select h.*,e.EmployeeName from history h inner join Employees e on e.ID = h.UserID

Then the datset will be modified to include the column EmployeeName on all rows in the History table. Then the column "employeename" is directly available in yyour report

Here is an example of how to join your employee and history tables with SQL. My Access is rusty, so the syntax might not be perfect, but this should demonstrate the concept. Obviously, I made up the column names, so you will have to change the columns to whatever you need.

SELECT EmployeeID,
       EmployeeName,
       TimeIn,
       TimeOut
FROM   Employee Emp
       INNER JOIN History History
          ON Emp.EmployeeID = History.EmployeeID