DataBase - Copy selected records from two tables to two new tables

Asked By alhotch

31-Jan-10 01:12 PM

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 will 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 am 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) ?

31-Jan-10 01:45 PM

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
santus@penn.com

31-Jan-10 07:00 PM

This is not a good way of going about things, as you would be encoding data
(the fact that orders are culled) as a table name.  A fundamental principle
of the database relational model (the Information Principle) is that data is
stored as values at column positions in rows in tables and in no other way.

Notwithstanding this fundamental design flaw, you are making life a lot more
difficult for yourself than needs be the case.  All you need to do is include
a Boolean (Yes/No) column, Culled, in the orders table.  To 'cull' an order
you simply set the value of this column to True, which you can do via a bound
checkbox on a form or with an 'update' query if you want to cull a set of
orders simultaneously.

The order details table need not be changed as the related rows in it will
still be related to the rows marked as culled in the orders table.

For any forms or reports where you only want to see the active orders just
base the form or report on a query 'WHERE Culled = FALSE'.  In query design
view simply enter False in the 'criteria' row of the Culled column.
Conversely to see only the culled orders the criterion for the Culled column
is True.

Ken Sheridan
Stafford, England


--
Message posted via http://www.accessmonster.com

31-Jan-10 07:03 PM

Rather than copy records to two new tables, cannot you simply add a Yes/No
field to your existing Orders table, with a default value of 0 (false) for


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