AnsweredAssumed Answered

NEWBIE - database relations

Question asked by googlit_1 on Jun 17, 2009
Latest reply on Jun 17, 2009 by googlit_1


NEWBIE - database relations




I am trying to create a simple order database using three seperate tables ('sales_order', 'product' & 'Sales_Order_Product')


what i am trying to do is display a basic screen which will hold the following information about the customer, (info in brackets represents {table name, field}


-Order No   (sales_order, sales_order_ID)

- Name      (Sales_Order, Name)

-Address    (Sales_Order, Address)

-Telephone (Sales_Order, Telephone)

-Email         (sales_order, email)

-Order Date (sales_order, order_date)

-Deal Price   (sales_order, deal price)


-Product Purchased with price information -


this is where the problem begins, i want to have a field or drop down box where the user can choose the product (held in the 'Products' table) and associate it with the order. i have read up on relational databases and been informed that the easiest way is to have a relating table (hence 'Sales_Order_Products' table ) so i created 'Sales_Order_Products' with the follwing fields 'Sales_Order_ID' & 'Product_ID'. i have added relationships between the follwoing


Sales_Order.Sales_Order_ID = Sales_Order_Product.Sales_Order_ID 

Product.Product_ID = Sales_Order_Product.Product_ID


but am unable to acheive the desired results,


to summerize i want the programme to work as follows, as the user inputs new data it should write customer info to 'Sales_Order' table and then referance the products from the 'Products' table placing the results in the 'Sales_Order_Product' table


for example; order number 1000 is input, the customer data (name, address etc) is written to the 'Sales_Order' table with an 'Sales_Order_ID' of 1000, the following products are then added, ProductA with a 'Product_ID' of '1', and ProductG with a 'Product_ID' of '7'which information is sourced from the 'Product' table, because these have been added the following referance should be written to the 'Sales_Order_Product' table:


  ||Sales_order_ID   ||   Product_ID    ||


1 ||       1000         ||          1          ||


2 ||        1000        ||          7          ||



Any help would be appreciated as i highly impressed with the capabilities of this programme already