delete duplicate customer profile but not the detailed transaction

[mys]

hi!i have a customer profile table. some with duplicate names but different
entries on each xc: different date & transaction of the same customer. How do
I merge the details under one customer profile only? So when I open up one
name I can see all transactions a customer made?

Also, if a customer have two vehicles for example. Can i have only one
profile for the two vehicles? and under each vehicle are each of their
service history?

I do have tables for the vehicles and table for the service history but most
are duplicated under the customer profile.

Thanks for all the help

 

[Allen Browne]

This question gets asked suprisingly rarely.

You probably have several tables, set up with one-to-many relations (through
Relationships on the Tools menu.) If you decide that customer 54 is the same
person as customer 22, so you want to merge their records, you need to
reassign all the related records for customer 54 to customer 22. Then, when
customer 54 no longer has any related records, you can delete him.

That means you will need to execute an Update query statement for each table
that has CustomerID as a foreign key. For example, to reassign all records
in tblInvoice you would execute this query:
UPDATE tblInvoice SET CustomerID = 22 WHERE CustomerID = 54;
Do the same for any other related tables. If you want to do this
programmatically, it will look like this:

Dim db As DAO.Database
Dim strSql As String
Set db = dbEngine(0)(0)

strSql = "UPDATE tblInvoice ...
db.Execute strSql, dbFailOnError

strSql = "UPDATE SomeOtherTable SET ...
db.Execute strSql, dbFailOnError
'and so on for other related tables.

strSql = "DELETE FROM tblCustomer WHERE CustomerID = 54;"
db.Execute strSql, dbFailOnError

You probably want to wrap this in a transaction, so you get an
all-or-nothing result.

 

[John Vinson]

hi!i have a customer profile table. some with duplicate names but different
entries on each xc: different date & transaction of the same customer. How do
I merge the details under one customer profile only? So when I open up one
name I can see all transactions a customer made?

Also, if a customer have two vehicles for example. Can i have only one
profile for the two vehicles? and under each vehicle are each of their
service history?

I do have tables for the vehicles and table for the service history but most
are duplicated under the customer profile.

Thanks for all the help

You may be able to run an Update query updating the foreign key in one
of the duplicates' related records to the value of the Primary Key in
the other record for that customer - this would "repoint" all the
related records to the same master record.

Just how you would do that depends on your table structure and
relationships, which of course you know and we don't.

John W. Vinson[MVP]

 

[mys]

Hi- so how do I update the tables? where should I go? I didn't see anything
there in access I can click that say Update Query...
Sorry I am a beginner

 

[Allen Browne]

Create a query.
Add the table you want to update.
Drag the field(s) you want to update into the grid.
Choose Update on Query menu. (Adds an Update row to the grid.)
Type into the Update row, the new value for the field.
Run the query (Run on Query menu.)

Or, if you want to create a query in code, you can switch the query to SQL
view to see the query statement you need to execute in code instead of
executing it.