Copy selected records from two tables to two new tables

[alhotch]

I have a database with two tables - tblOrders and tblOrderDetails.
Relationship of One (tblOrders) to-Many (tblOrderDetails) exists. tblOrders
has fldOrdersID as Primary Key and tblOrderDetails has fldOrdersID as Foreign
Key. As expected, the tblOrderDetails table can have more than one
corresponding record in the tblOrders table. I need to select records (based
on the fldOrdersID) from the tblOrders table AND "child" records from the
tblOrderDetails table to TWO NEW tables - I'll call them tblCulledOrders and
tblCulledOrderDetails. This means I get a copy of the selected records ONLY,
in the new tables.

I have created an Append Query that selects the desired record from
tblOrders and places it in the tblCulledOrders table. However, I do not get
the corresponding "child" record from tblOrderDetails table to be placed in
the tblCulledOrderDetails table. I'm not sure how to set up the queries and
relationships between (among ?) the two or four table to do this. Also, if
the tblOrderDetails table has more that one corresponding record to the
tblOrders table, Access appends two (or more) records to the tblCulledOrders
table after issuing a "key violation" message prior to performing the Append
Query operation.

Can this operation be done without writing any code (I quit writing code -
Fortran - years ago) ?

 

[Steve]

Your append query with the appropriate criteria will append the order
records you want to tblCulledOrders. Note that you MUST do this first before
trying to append detail records to tblCulledOrderDetails.

To append detail records to tblCulledOrderDetails, you need a second append
query. The query needs to include tblOrderDetails and tblCulledOrders. Join
OrderID between the two tables. THis makes the query only return the detail
records that correspond to the orders in tblCulledOrders. Run the second
query and you will append all the detail records that correspond to the
orders in tblCulledOrders.

Steve
(e-mail address removed)

 

[Tom Wickerath]

Rather than copy records to two new tables, can't you simply add a Yes/No
field to your existing Orders table, with a default value of 0 (false) for
"Culled Order"?


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________