2 Replies Latest reply on Jun 17, 2009 8:53 AM by googlit_1

    NEWBIE - database relations



      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