Merging Records

[cga1982]

I have an Access 2003 database with a table for customers and a table
for orders - linked by the cutomer number (a unique field in the
customer table).

Sometimes the staff set up a new customer when there already is the
same existing customer in the system. Therefore the system gives this
same customer a new unique customer number and the staff enters orders
against this new customer number.

How would I merge the new customer data with the old customer?

I would like to enter the new customer number into a box on a form and
then enter the old customer number into a box on the same form and have
the system move all of the history from the new customer to the old
customer.

Thanks for your help.

[Jana]

cga:

Add a command button to your form, with code something like this:

Dim MySQL As String

MySQL = "UPDATE orders SET [customer number] = " _
& Me![old customer number] & _
& " WHERE [customer number] = " _
& Me![new customer number]

DoCmd.RunSQL (MySQL)

HTH,
Jana

[Jana]

cga:

BE SURE TO TEST THIS ON A COPY FIRST!!!