If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Linking tables
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 |
Thread Tools | |
Display Modes | |
|
|