table with multiple fields with lookup to same table

[J Dave]

I will relate my issue to the Northwinds sample db. I would like my "orders"
table to contain fields for the products in the order (let's say 3 products)
that would have a lookup to the products table. Can anyone please tell me how
I could do this, or explain what other method to use.

 

[Bob Quintal]

I will relate my issue to the Northwinds sample db. I would like
my "orders" table to contain fields for the products in the order
(let's say 3 products) that would have a lookup to the products
table. Can anyone please tell me how I could do this, or explain
what other method to use.

The way to do this correctly is to have an orders table with no
products and a child table with the order number and one product number
per row, 3 rows. Then you don't have to relate three times.

 

[John W. Vinson]

I will relate my issue to the Northwinds sample db. I would like my "orders"
table to contain fields for the products in the order (let's say 3 products)
that would have a lookup to the products table. Can anyone please tell me how
I could do this, or explain what other method to use.

Go the next step: actually relate it to the Northwind sample.

"Fields are expensive, records are cheap". You don't want or need three
Products fields; instead, you need any desired arbitrary number of
OrderDetails *RECORDS* in a table related one to many to Orders, and also to
Products.