Adding and Deleting records simultaneously in multiple tables

[Bobk]

I have inherited a table called "Bookings". This table cotains summary sales
order data. It was loaded via a Form. I have been asked to build a detailed
order entry system with an order entry form that will record all detailed
information associated with an order. This is ok. The problem I'm haveing is:
I want to be able to automatically update the existing Bookings table. When
new orders are entered through the order entry form, I want them to appear in
the bookings table. All new order data entered through the order entry form
is held in a table called "oeheader". Orders entered into the bookings table
prior to using the new order entry form do not appear in oeheader. The order
entry form is driven from a query and contains the bookings table. I have the
bookings table related to the oeheader table via the keys in both tables.
When I try to add or delete a record from oeheader I am only able to modify
the oeheader table. Can anyone point me in the right direction?

 

[Klatuu]

Before you implement the new design, you need to convert the data in your
existing Bookings table to the new format.

Your new design should have two tables. One for the header info and a
related detail table that contains line item detail for the order.

 

[Bobk]

I have set up a table called oeheader and oeline to handle the new order
entry. The only thing I am having trouble with is adding new data to the old
Bookings table. When I enter data into oeheader I want to be able to add data
to the Bookings file at the same time.

 

[Bobk]

Before you implement the new design, you need to convert the data in your
existing Bookings table to the new format.

Your new design should have two tables. One for the header info and a
related detail table that contains line item detail for the order.

 

[Bobk]

I don't think I have explained my problem very well. What I have is an
existing Bookings table with several hundred records. The records contain
data that you would typically find in an Order Header table like customer
name, PO number and amount and due date. I have created a real order entry
system which will contain all the same data fields that are now existing in
the Bookings table. All I want to do is add more records to the old Bookings
table when I enter a new order from the new order entry system. The new order
entry system will have data entered in a table called oeheader. The oeheader
table will only contain newly entered data. What I want to do is be able to
add, delete, update and edit the new entries in the Bookings table through
the new order entry system and have this happen automatically when new orders
are entered or changed.

 

[Klatuu]

What would be the purpose of keeping the data in the old table if you are
replacing it with the new system?

What you are wanting to do is not that simple. It will involve some queries
to update your old bookings table. You can execute the queries from the
form where you enter data for the new header table.

 

[Bobk]

I think that is what I want to do. I am trying to avoid disturbing what is
already in place. I just don't know how to do it.

 

[Klatuu]

I would suggest replacing the old way of doing it rather than complicate the
application trying to keep two versions of the same thing. That will, of
course, require some remediation to other parts of the application, but in
the long run, it would be better. If that is too daunting a task, there is
one other possibility. That would be to not update the old table as you are
entering data in the new tables, but create queries to update the old table
with the data in the new tables and only do the update when you need to use
the data in the old format.

 

[Bobk]

Yes, I think an update query might be the quickest way to get this done. For
a large table it might be a little slow. At least I wouldn't have to rewrite
a lot off stuff. I just didn't want to have to get into the existing stuff
that works ok now. Thanks for the help.