Automatically create new record to related table on update

[md7546]

Hi, first time posting to this group, but I have followed advice and
examples for several months, so thank you.

I work for a small telecommunications company. I have a simple Orders
database that has an Orders table to track a sales order, using
OrdersID as the PK. I have a related one-to-many table called
OrderDetails, with the OrdersID as a FK, which shows all the products
sold with that order. Then I have an Update table, in a one-to-many
relationship with the OrderDetails table (OrderDetailID is FK in Update
table). This table is used to track the status of each product as it
flows through the order process. Each product can have different status
dates and types.

On a monthly basis, I import my Order information into the Orders and
OrderDetails table from a .csv file. (Sales information comes from a
Siebel export). The PK for the OrdersID is a unique Order ID that is
created from the Siebel database. No issues there. I also am able to
import the OrderDetails information without any issue.

What I would like to do is have Access automatically create a new
record with the UpdateRecordID PK field updated in the Update table for
each OrderDetailID when I paste them into the OrderDetail table.

The reason I need this is I run a query that shows me the current
status off all OrderDetails. I can only see the Update records that
contain an updated PK field value.

[Smartin]

md****@sbc.com wrote:

Hi, first time posting to this group, but I have followed advice and
examples for several months, so thank you.

I work for a small telecommunications company. I have a simple Orders
database that has an Orders table to track a sales order, using
OrdersID as the PK. I have a related one-to-many table called
OrderDetails, with the OrdersID as a FK, which shows all the products
sold with that order. Then I have an Update table, in a one-to-many
relationship with the OrderDetails table (OrderDetailID is FK in Update
table). This table is used to track the status of each product as it
flows through the order process. Each product can have different status
dates and types.

On a monthly basis, I import my Order information into the Orders and
OrderDetails table from a .csv file. (Sales information comes from a
Siebel export). The PK for the OrdersID is a unique Order ID that is
created from the Siebel database. No issues there. I also am able to
import the OrderDetails information without any issue.

What I would like to do is have Access automatically create a new
record with the UpdateRecordID PK field updated in the Update table for
each OrderDetailID when I paste them into the OrderDetail table.

The reason I need this is I run a query that shows me the current
status off all OrderDetails. I can only see the Update records that
contain an updated PK field value.

Hope I followed you correctly here... So you want Update to contain all
the OrderDetailID's? Would it suffice to work this into your existing
query (or create a new query), along the lines of

SELECT DISTINCT OrderDetailID from OrderDetails;

?
--
Smartin