Linking tables

Help please

Im stuck! I have two tables one for enquiries and another for orders. I
have all the contact details in the enquiries and want this to transfer over
to the order table if the enquiry porceeds to an order. At the moment I am
re-inputing all the contact details to the order form. Can anyone help?

Many thanks

You can execute an Append query statement to copy the data from one table to
another.

You will need tables like this:
a) Client table (one record for each person/company you deal with), with a
ClientID primary key

b) Product table (one record for each thing you sell/supply) with a
ProductID primary key.

c) Enquiry table (one record for each enquiry/quote), with an EnquiryID
primary key, ClientID, and EnquiryDate.
- EnquiryID primary key
- ClientID who placed this order
- EnquiryDate Date/Time

d) EnquiryDetail table (the line items of the enquiry), with fields like
this:
- EnquiryDetailID primary key
- EnquiryID which enquiry this row belongs to
- ProductID what product the client enquired about
- Quantity how many of this product they wanted
- PriceEach the unit price you quoted

The point of table (d) is that a client could enquire about many products at
the same time. By creating a one-to-many relation between (c) and (d), an
enquiry can have as many line items as you need (just one row if they asked
about one product, or many rows if they asked about many.)

e) Orders table (one record for each order a client places with you) with
fields like this:
- OrderID primary key
- ClientID who placed this order
- OrderDate Date/Time
- EnquiryID the enquiry this order came from (blank if there was none.)

f) OrderDetail table (the line items of the order), with fields like this:
- OrderDetailID primary key
- OrderID which order this row belongs to
- ProductID what product the client ordered
- Quantity how many of this product they ordered
- PriceEach unit price

Since table (e) has a similar structure to (c), and (f) is similar to (d),
you can put a command button on your Enquiry form to turn the enquiry into
an order. This button will need to execute some VBA code that adds the
records to the other tables.

You need the OrderID of the newly created order so you can use it in the
OrderID field or the OrderDetail table. The simplest way to do this would be
to OpenRecordset() and AddNew with Update. Then get the new OrderID and use
it in the SQL statement that appends the related records to the OrderDetail
table.

Writing that code for you is beyond a newsgroup post, but here's an example
of some code that uses AddNew followed by an Append query execute:
Duplicate the record in form and subform
at:
http://allenbrowne.com/ser-57.html

Your code may want to do other checking as well (e.g. to warn if the enquiry
has already been turned into an order.)
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Lyonrem" wrote in message
...
Help please

Im stuck! I have two tables one for enquiries and another for orders. I
have all the contact details in the enquiries and want this to transfer
over
to the order table if the enquiry porceeds to an order. At the moment I
am
re-inputing all the contact details to the order form. Can anyone help?

Many thanks