Modify existing jobsheet database

[chris wells]

I have a jobsheet database containing an orders table. Its got almost 2000 entries in it.
Each record contains customer name in full together with other fields for job no, date, description,invoice no
amount and a numerical job status. Since many customers repeat jobs I would like to add a customer contact table and use the customer data from that table in the orders table.
I tried a copy of the empty database and containing a lookup for customer in the new contacts table- that works ok.
any kind of query or append to transfer from the old orders database to the modified new one fails.
I want to delete all the full customer details from the orders table and replace them with a lookup from a new contacts.
I thought of exporting the customer names to a new contacts table but there are duplicates.
any ideas please


Thanks

[June7]

Can be done, carefully.

What is nature of 'duplicates' - exact duplicates of customer names or some differences in spelling for the same customer? If misspelling is an issue in the Orders table will have to edit the records to get consistency in names.

Then create contacts table with unique customer records. Create a query that will get unique names from Orders table.
SELECT DISTINCT CustomerName FROM Orders;
Make Contacts table with records from that query.
Add an autonumber field to create unique ID.

Create a number field in Orders table for the Contacts table unique ID.

Build UPDATE query that joins the two tables on the common customer name fields and update the new fk field in Orders with the new pk field in Contacts. When all is good, remove customer name field from Orders table.