Normalization

[Vav]

Hello all....

This is more of a general question about building a form based off several tables...

If i have a tbl_customer table, a tbl_book table and an tbl_order table.

I fully understand the principle behind foreign keys and and making relationships between tables.

For example the orders table among other fields would have customer_id and book_id as the foreign, as to reduce the possibility of redundant data being stored in the orders table.

My question is such.... How am i supposed to create the orders form. I know have to create the form having the record source as tbl_Order, but how do i get things like for example customer name, address, book name, number of pages etc... to appear.

And further more how would i get it to store the appropriate data in the approiate tabel. Cause as it stands right now the way i see it, the order table would only save the info on the customer_id and the book_id, but where would the other info go.

1- Does Access automatically store the info in the appropriate tables once the relationship have be established.

2- Do i have to create a query that pulls all the information from the three different tables, and base the Record Source of the Orders table off that query. Cause if that is the case when then would i actually have to create the relationships.

Thanks you in advance for anyone wanting to help me get a little clear on this subjuect.

VAV

 

[dynamictiger]

I think you also need an orders detail table. That aside.

The principle of normalisation is to store information only once, and enter only once. That being the case if you want to display information about your book from your book table in the orders form then yes you do this by a query or similar arrangment.

I think from you post that the customer places the order and therefore the customerID is recorded with the OrderID, however, I do not think the bookID should be recorded there.

Rather the bookID should be a foreign key in the tblOrderDetail. tblOrderID should be a foreign key as well. In this way any customer can have many orders and any order can have many books.

HTH

 

[Pat Hartman]

Take a long hard look at northwind.mdb and orders.mdb. They are both order entry systems which is what you are trying to build.