AnsweredAssumed Answered

NEWBIE - database relations

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

Title

NEWBIE - database relations

Post

Hello,

 

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

 

Thanks

Outcomes