Reverting Short-Text Field Value In Table To Matching Primary Key From Another Table

[CodyL]

Hello,

For Context:
I have created a database for my company to better maintain data that production workers were entering into an Excel file online. Instead of going through the hassle of entering the data into Excel in the browser I compiled the existing data from Excel into a coinciding table (MainData_tbl) in Access and created a Front End Form (Inspection_frm) that is located on all of the production computers.

To compliment the form I've also created multiple tables to give row sources to the combo boxes.

Issue:
To get to the point we are going to be using this database for a lot of analysis and when we do analyze the data we only want to see numbers, no names. The data I compiled used names for employees however I configured the form to show names for the employees combo box but write employee numbers to the 'MainData_tbl'. The row source for the employee combo box in the 'Inspections_frm' is from a table I created with just the employees names and their employee ids, the employee ids being the primary key (EmployeeDirectory_tbl).

So the data for employees in 'MainData_tbl' shows names for what I compiled from the Excel, and numbers for what has been input since I launched the Form for the employees to use. The latter is what is desired but as I compiled from Excel the older data is not in the desired format.



I need some sort of direction on how to change the employee names, from the compiled Excel data, to their respective employee numbers.

[ranman256]

put the EmpID field in the tMainData table,
then make an update query, join tMainData and tEmps on name,
write in the tMainData.EmpID from tEmp.EmpID.
Some will update , some may not due to spelling difference.

now, either Add these different spelled names to a sub table of tEmps, tEmpAltNames.
collect a persons different spellings and his ID,
21,John Smith
21,Jon Smith
21,Johnathan Smith

then youll eventually be able to update ALL excel data from this table.
but tMainData will have all the EmpID filled.

[CodyL]

Thanks for the input, however I just used "Find and Replace". I apologize for the confusion but here is some more information in case you are curious.

The form I made replaced the way they were recording inspection data. They were entering the data in Excel online and just typing their names in, which like you suggested provided for intriguingly vast deviations. The form I created in Access gives a restricted combo box for Employee Names which is sourced to a table with Employee Names and Employee Numbers. For data analysis we don't want to see names, just employee numbers, so the drop down on the form shows names for the employees to see and select but when submitted returns Employee Numbers to the "MainData_tbl" table.

My problem was that all their previous data that I imported from Excel had their names, not numbers. So anything they input after the original import was correct but I still had to format the old data correctly for data analysis.