How do you insert rcds into a relationship table

[Guest]

I am new at using Acces. This is my first project. I have created 3 tbls,
tbl1, tbl2, tbl3. tbl3 is a relationship tbl between tbl1 and tbl2. it is
one-to many tbl1-tbl2. i am having trouble creating a form to insert rcds
into tbl3. i created a form which has a subform in it. the main form shows
1 rcd at a time from tbl1. the field displayed is itemName. the key to tbl1
is itemId. the subform shows all the rcds in tbl3. i want to add addition
rcds to tbl3. when i type in the subform field * i get a message which says
"Field cannot be updated". am i doing this all wrong? all i really need is
a way to show rcds in tbl1, rcds in tbl2 and be able to select certain rcds
from tbl2 to be associated with a rcd in tbl1.

 

[Martin Widmer]

Hi Brian

For 1:n relationships you don't need a relationship table. Relationship
tables are only necessary for m:n relationships. Get rid of one table and
save the primary key of the first table in a foreign key field on the second
table. Then build a query from that or use this in a form-subform construct
to enter data.

Martin

 

[Guest]

Martin

thanks for the help. i think i am still confused. let me give you an
example:
tbl1 (Order)
orderId
OrderName
....

tbl2 (item)
itemId
itemName
.....

an order can have many items. therefore i have an relationship tbl

tbl3 (orderitemRel)
orderItemRe;Id
orderId
itemId


ex:
order:
orderId orderName
1 IBM123
2 Ford234

item:
itemId itemName
1 paper
2 pen
3 tape

orderItemRel:
orderItemRelId orderId itemId
1 1 1
2 1 3
3 2 2
4 2 1
5 2 3

how would this be done if i did not have the relationship table?

 

[Rick Brandt]

Martin

thanks for the help. i think i am still confused. let me give you an
example:
tbl1 (Order)
orderId
OrderName
...

tbl2 (item)
itemId
itemName
....

an order can have many items. therefore i have an relationship tbl

tbl3 (orderitemRel)
orderItemRe;Id
orderId
itemId

All you need is an OrderID field in your item table. That is the foreign key
that indicates which order the item is related to. No need for a third table.

 

[Guest]

i cannot put orderId into the item table. the item table is a list of all
available items for sale. an item like paper will occur in many orders.
that is why i have the relationship table. item to order is many-to-many.