Append data from (1) table into (2) tables

[Hdata]

Goal: Append Data from one table [TestData-AA]
into two related tables: tblLandSales and tblDeedData.

Don't think possible from Query Design window,
maybe from SQL Design window? Maybe right or left join?

1st Append: Primary table tblLandSales　
Set rstLandSales = db.OpenRecordset(" Select * " & _
" from qryLandSales" & _
" where Checkbox1 = " & True)
　
INSERT INTO tblLandSales ( SaleDate, CashEquiv )
SELECT [TestData-AA].SaleDate, [TestData-AA].CashEquiv
FROM [TestData-AA];
.............................................
2nd Append: To related table tblDeedData
　
Set rstLandDataDeed = db.OpenRecordset(" Select * " & _
" from qryLandDataDeed " & _
" where LandSalesID = " & rstLandSales!LandSalesID)
　
INSERT INTO tblDeedData ( DeedBook, DeedPage )
SELECT [TestData-AA].DeedBook, [TestData-AA].DeedPage
FROM [TestData-AA];

Thanks again!

 

[Ranman256]

make both queries.
put both queries into a macro.
make a button to run the macro.
both queries will then get run when clicked.

 

[Hdata]

I think that would be a quick and easy solution, however I believe that would loose the One:Many relationship between the tblLandsales table and the tblDeedData table creating an orphan table? Thanks anyway, although that thought may just raise the idea for a solution if one is available?

 

[sneuberg]

I don't see the primary and foreign keys in these tables. If they are not added you definitely won't have a One:Many relationship or really any relationship at all. What are these keys?

I don't know if it is relevant to your problem but I was reading this thread about retrieving the last autonumber inserted. This would be relevant if the primary key of tblLandSales is an Autonumber. You would need to retrieve that to insert into tblDeedData as the foreign key. As long as you got the right Autonumber from the first append it would make any difference how long it took you to append into the tblDeedData.

 

[Galaxiom]

I think that would be a quick and easy solution, however I believe that would loose the One:Many relationship between the tblLandsales table and the tblDeedData table creating an orphan table?

No it won't affect the table relationships. You are just inserting records into them. However there doesn't appear to be any relationship between the records you are inserting into the two tables.

Opening the recordsets doesn't achieve anything. Nor does concatenating True into an SQL command.

although that thought may just raise the idea for a solution if one is available?

Two queries is the only solution in Access.

 

[Hdata]

Goal: Append Data from one table [TestData-AA]
into two related tables: tblLandSales and tblDeedData.

OK: Step 1 success with obtaining the Primary key # via @@identity from the (One-side) of the relationship identified as LandSalesID in tblLandSales. The (many-side) is identified as tblDeedData with a primary field key of DeedDataID. Also, a foreign key in tbldeedData identified as F_LandSalesID.

Step 2: is to export from a 3rd table identified as TestData-AA (containing all the data) with some fields going to tblLandSales, and other fields going to tblDeedData.

I have reviewed some of the posts regarding combining queries, however I did not see anything matching my situation. So at this point I'm requesting help, maybe other posts I missed? or some direction. I've been told two queries will be necessary. Thanks in advance.