Copying a record from multiple tables

[Sandpiper]

Hi.
I am trying to copy a record which has data in 3 tables, and replicate it in the same set of tables, but with a different ID number.

I know how to write an InsertInto statement, but what i'm not sure of, is how to copy the new id (that has been set by autonumber) into the other 2 tables, so that the record remains linked and whole.

In tblJobs, I have JobID (primaryKey), Date, Customer,OrderNumber
In tblLoads, I have LoadID, JobID, PUPostcode, DELPostcode
in tblVia, I have ViaID, LoadID, VPUPostcode, VDELPostcode

1 job can have many loads, and 1 load can have many Vias.

If a job is repeated, I need to copy the job, including the load and via details, and duplicate it with the latest ID number. My ID numbers are created using AutoNumber.

If anyone could help me with how to write the multiple insertinto, i'd be very grateful.

Thanks

 

[rainman89]

is the data going to be the same for each job no matter what?

If that is the case, you may want to create another table that has an autonumber ID number, date and jobID number in it that way you dont end up with duplicate data in your database. And use your autonumberID as the new job #

 

[Sandpiper]

Thanksf or the response, but unfortunatley I need to keep them together, so that all jobs have a continuous jobid.

 

[gemma-the-husky]

requests like this generally point to a design/normalisation problem

what is your system trying to do?

 

[Sandpiper]

Hi
The database is controlling jobs for a delivery company.
I have one table which store the job header, one which stores the details of the loads, and one which stores via points.

One job can have many loads (although it is not normal), but one load can have many drop off points (vias).

What is also common is that a company can request the same job on a regular basis. (for example, deliveries of newspapers.
the job is for the printer, the load details specifies that they are collecting from point A the printer and ending up at point B the newsagent, but on the way, they will also have to make drops at 3 other newsagents (vias). This happens every day.

The user doesn't want to have to input the same details over and over again, they want to be able to copy the first instance of the job and replicate it to create a new job, but on a different day.

Does that make sense?

That is what I am trying to do.

So I can copy the job details and create a new record in the job table, but I'm struggling with then picking up the new job number so that when I replicate the delivery information it is all linked together.

Thanks

 

[gemma-the-husky]

i would be inclined to try and find a way of holding the repeatable jobs in a separate structure, and then append them from there. that way you can more easlily add a new viapoint to your route.

but when you add the route - just store the routeid somewhere - and then use that to set the other records.

you can probably do it as you are suggesting by just cloning an existing route - you just need to plan it logically
clone the route
then clone the loads
then clone the viapoints
etc

it might need a combination of code and queries - depends how complex it is

---------
i've done a lot of logistics stuff - tbh, we actually end up in most cases summarising the routes/drops etc in a spreadsheet, and importing the spreadsheet

 

[Sandpiper]

Thanks, that does make sense, but even if I exported to excel and then imported again, i'd still have to pick up the new job number and use it in the import for the loads and vias etc. as it's still got to sit in 3 tables, that is the bit i'm struggling with.