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

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"mys" <(E-Mail Removed)> wrote in message
news:A14DF1AE-A29D-483F-807D-(E-Mail Removed)...
> 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

On Fri, 20 Jan 2006 20:35:02 -0800, mys
<(E-Mail Removed)> wrote:

>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]

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" wrote:

> 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.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "mys" <(E-Mail Removed)> wrote in message
> news:A14DF1AE-A29D-483F-807D-(E-Mail Removed)...
> > 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
>
>
>

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"mys" <(E-Mail Removed)> wrote in message
news:10466D8D-90C5-4CDC-98AE-(E-Mail Removed)...
> 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" wrote:
>
>> 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.
>>
>> "mys" <(E-Mail Removed)> wrote in message
>> news:A14DF1AE-A29D-483F-807D-(E-Mail Removed)...
>> > 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.