Append to 2 tables with master/detail relationship

[cloverdog]

Hello. I need to be able to append records from a mobile database to a main database whilst maintaining relationships. For example a salesman returns with new orders contained in a master(tblClient)/detail (tblContract) relationship. The tables are exactly as in the main database with both tblClient and tblContract having autonumber ID fields. The salesperson’s autonumber ID fields form a vital part of the Master/Detail which will need to be kept in the main database.

If there were no relationships I could just do something with an append query but as far as I am aware there is no way to import and append a set of master/detail records easily into two tables with the same relationships.

Thank you in advance for your help.

 

[lespaul]

have you looked into the replication features? I believe that's what they are designed to do.....

Leslie

Have you met Hardy Heron?

 

[cloverdog]

Hello Lespaul

Thanks for getting back.

Not realy but I have seen what 'Briefcase' does to the ID fields. Another point I didn't mention is that I don't want the database replicated. A mobile database is totally empty other than the clients put on that day by the salesperson. Also a salesperson will have only a small part of the main database structure of say 2-4 tables out of 50.

 

[traingamer]

You have to maintain the relationships, not the actual autonumber ids.

I'm assuming that the salesman is entering new records in both the tblclient and the tblContract tables. (Otherwise, you shouldn't have a problem).

From your master database, link to the tables in the mobile database. Select the new client records from there and append them to the master client table (do not include the id - instead let new ids be generated in the master).
Then append the remote contract to the master contract using the new foreign key from tblclient.

Insert into tblContract (clientid, contractdata1,...)
SELECT m.clientid, rc.contractdata1, rc.contractdata2...
from tblclient m 
inner join rmttblclient rcl on ...matching criteria... inner join rmtContract rc on rc.clientid = rcl.clientid

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[cloverdog]

Thanks for this post Greg. You are right that the the salesman enters both client and contract details in the house.

Your reply is what I am looking for but I can't get my head around the relationships part especially 'matching criteria'. Does it mean that the tblRemoteClient should link to the tblClient by something else such as PostCode?. Or is the second inner join somehow part of the first?

 

[traingamer]

You may actually have to use an intermediate table to import all the 'new' records from the mobile database.

I put the horribly vague ...matching criteria... because I do not know what data you have in your records. It could be something as simple as with m.clientname = rcl.clientname. Or it may be that you'll have to add a column to the client table (e.g. rmtclientid) to be able to match (then clear the column after import so it won't potentially match the next mobile database imported).

There are many wasy to do this. As I type, I'm thinking that an intermediate client table (e.g. tblimportclient) might be your best bet. Use the actual mobile clientid in that intermediate table, run your imports, then clear the intermediate table before the next mobile import.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard