C
cjg.groups
How can a form and subform update an intersection/junction table used
to maintain their many-to-many relationship? Also, please advise re:
table structure, synchronization, and relationships/linking.
Goal:
Orders can contain 1 to 4 Contacts. Contacts can associate with
multiple orders. The OrderForm contains the ContactSubForm which
should only show Contacts on that order.
Tables:
Orders - OrderID (PK)
Contacts - ContactID (PK)
RelOrdersContacts - OrderID (PK), ContactID (PK) (can I have two PKs?)
Query:
ContactsForCurrentOrder -
SELECT RelOrdersContacts.OrderID, Contacts.*
FROM Orders INNER JOIN (Contacts INNER JOIN RelOrdersContacts ON
Contacts.ContactID = RelOrdersContacts.ContactID) ON Orders.OrderID =
RelOrdersContacts.OrderID;
Forms:
OrderForm - based on Orders table
ContactSubForm - based on ContactsForCurrentOrder query, Master/Child
Fields linked on OrderID.
Orders and Contacts are related many-to-many using the
intersection/junction table RelOrdersContacts. Is there a better way?
Can I store other data there besides OrderID and ContactID?.
When I add new records to the Contacts subform, can it update
RelOrdersContacts to associate the Contact with the form's currently
shown Order? Is there a better way to maintain this many-to-many using
forms?
To establish the Master/Child Fields link, I included OrderID in the
subform's record source query. Now I get "Field can not be updated"
errors when adding new subform records (because the main form and
subform query are accessing Orders?). Should I remove the link and use
query criteria [Forms]![OrderForm]![OrderID] to show contacts for the
current order?
Why the nested INNER JOIN in the query (generated via GUI) for the
relationship? Why not WHERE Orders.OrderID=RelOrdersContacts.OrderID
AND Contacts.ContactID=RelOrdersContacts.ContactID?
This is my first database and after 9 hours reading Newsgroups, I've
made little progress. I'm tempted to do it "the wrong way" just to get
it done. Thank you very much for your help.
to maintain their many-to-many relationship? Also, please advise re:
table structure, synchronization, and relationships/linking.
Goal:
Orders can contain 1 to 4 Contacts. Contacts can associate with
multiple orders. The OrderForm contains the ContactSubForm which
should only show Contacts on that order.
Tables:
Orders - OrderID (PK)
Contacts - ContactID (PK)
RelOrdersContacts - OrderID (PK), ContactID (PK) (can I have two PKs?)
Query:
ContactsForCurrentOrder -
SELECT RelOrdersContacts.OrderID, Contacts.*
FROM Orders INNER JOIN (Contacts INNER JOIN RelOrdersContacts ON
Contacts.ContactID = RelOrdersContacts.ContactID) ON Orders.OrderID =
RelOrdersContacts.OrderID;
Forms:
OrderForm - based on Orders table
ContactSubForm - based on ContactsForCurrentOrder query, Master/Child
Fields linked on OrderID.
Orders and Contacts are related many-to-many using the
intersection/junction table RelOrdersContacts. Is there a better way?
Can I store other data there besides OrderID and ContactID?.
When I add new records to the Contacts subform, can it update
RelOrdersContacts to associate the Contact with the form's currently
shown Order? Is there a better way to maintain this many-to-many using
forms?
To establish the Master/Child Fields link, I included OrderID in the
subform's record source query. Now I get "Field can not be updated"
errors when adding new subform records (because the main form and
subform query are accessing Orders?). Should I remove the link and use
query criteria [Forms]![OrderForm]![OrderID] to show contacts for the
current order?
Why the nested INNER JOIN in the query (generated via GUI) for the
relationship? Why not WHERE Orders.OrderID=RelOrdersContacts.OrderID
AND Contacts.ContactID=RelOrdersContacts.ContactID?
This is my first database and after 9 hours reading Newsgroups, I've
made little progress. I'm tempted to do it "the wrong way" just to get
it done. Thank you very much for your help.