Duplicating a record and related records

[rjhartman]

Hi,

I wish to create an append query to duplicate a record and it's related records in two other tables which creates a sub-sub relationship.

Tables are:

OrderDetails --> Invoice --> Rates

Related Keys are:

OrderID
JobID
ItemNo

The OrderDetails table has a one to many relationship with Invoice and Invoice has a many to many relationship with a lookup table called rates. I can get the query to copy the OrderID to a new record but when it copies over the related JobID's from Invoice with ItemNo's from the Rates table, it duplicates the results, so that i get multiple jobID's, instead of multiple ItemNo's and one JobID. The SQL i have tried is:

INSERT INTO OrderDetails ( OrderID )
SELECT OrderDetails.OrderID
FROM OrderDetails INNER JOIN Invoice ON OrderDetails.JobID = Invoice.JobID
WHERE (((OrderDetails.OrderID)=[me].[OrderID]));


The WHERE clause is just a control on the form.

Any help would be appreciated.

Kind Regards

Richard

 

[rjhartman]

I solved this myself. I did away with the sub-sub relationship and just made it into a subtable. Taught me the value of KISS.

Richard