Changing names to ID numbers

[marksnwv1957]

I recieve Excel worksheets with information and then import this information into a table I have in access. I verify the data and as a last step I append the data to a table called AllReportData2012 which is used to pull information for my reports. Part of the Excel information that is imported to a new table includes the employee's name with work details in each row.



In my access report I do not use the employees name, I use their employee ID number. I have a table in my acess database that contains 2 columns. The employees Name and their associated Employee ID number.

After I import the Excel information to Access how do I set up a macro or programming that will look at the name in each row and change it to the correct Employee ID. I could have anywhere from 20 rows of recods to 600 rows of records.

Thanks in advance for any help.

[JoeM]

You don't really need to change anything. Just create a query between the two tables in Access, linking on the Employee name and returning the Employee ID with all the other fields you want.

See here if you need help in doing that: http://www.ehow.com/how_12392_create...ft-access.html

[orange]

Use a Query along these lines

Select FromExcel.*, YourAccessTable.EmployeeId
FROM FromExcel INNER JOIN YourAccessTable ON
FromExcel.EmployeeName = YourAccessTable.EmployeeName

Where FromExcel is the name of the Table populated with the Excel data and
YourAccessTable is the name of your table (I have a table in my acess database that contains 2 columns. The employees Name and their associated Employee ID number.)

This query would be used for your Report

OOPs: I see JoeM is quicker and not as long winded as I am.

[marksnwv1957]

Thank you. Your recommendation works perfectly

[orange]

Glad you have it working.