Append from Multiple Tables to Multiple tables

[nunan]

Hi all

I am having some trouble with an append query and would appreciate any help.

I have 3 tables for potential customers
Potentials (PK)
Calls
Appointments
All tables have a ContactID field (autonumber) and they are all linked through this.

And 3 tables for existing customers
Existing (PK)
Calls
Appointments
All tables have a ContactID field (autonumber) and they are all linked through this.

What I am trying to do is create an append query that copies all data from the 3 tables for potential customers (for a specific record) to the 3 tables for existing customers.

I can copy over the data from the Potentials table to the Exisitng table (as a new row) but my problem is, as the Existing table already has clients listed, I am not sure if I can copy over the data from the other 2 tables as they are linked by the ContactID, I would need to include this field and that would create duplicate entries in the primary key.

I hope this makes sense and any help is appreciated x

Thanks

 

[AlexCuse]

my problem is, as the Existing table already has clients listed, I am not sure if I can copy over the data from the other 2 tables as they are linked by the ContactID, I would need to include this field and that would create duplicate entries in the primary key.

I would try an update for these tables instead, joined on contact ID.

Hope this helps,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[AlexCuse]

Also, I don't think you can accomplish this with one query. Even if you can, it might be best practice to have one query saved for each set of tables.

Good Luck,

Alex

Professor: But what about your superintelligence?
Gunther: When I had that there was too much pressure to use it. All I want out of life is to be a monkey of moderate intelligence who wears a suit. That's why I've decided to transfer to Business School.
Professor: NOOOOOOOOOOOO.

 

[nunan]

Thanks Alex, I will try that

 

[davegulliksen]

Your main problem, as I see it, is that the customer's CustomerID will change when it moves from one set of tables to the other.

If the customer name is unique, you can append the customer name to the existing customer table, query the name to get the new customer ID and use that to append the other records from the potential tables.

If it's not unique, you could append a dummy name of the OLD customer ID into the names table as the customer NAME, get the new customer ID, append the other two tables' data using that key and then update the first table with the real name.

This will involve several queries, of course. That's unavoidable, I think.

Dave G

 

[lespaul]

I would have to say that your initial design is flawed. I would have had a single table with a field indicating whether the record is for a potential customer or an existing customer. Then all you would have to do is an update to the customer record changing type from P(otential) to E(xisting).

If possible, perhaps you should consider changing your tables. Read the fundamentals document below for more information on table design.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi