Help with Many to Many Relationships & Data Entry Forms & SubForms

[jhawk]

I posted this in "Database Design," but haven't gotten any hits yet, so I thought I might try over here since it also deals with Forms and Subforms.

I’m a Newbie who needs some help with many to many relationships and setting up data entry forms correctly.

So far, I’ve set up the following tables and corresponding fields:

co_individual
ind_cst_key (primary key)
ind_prefix
ind_first_name
ind_middle_name
ind_last_name
ind_suffix
ind_credentials

co_customer [junction table]
cst_key (primary key)
cst_ind_key
cst_org_key

co_organization
org_cst_key (primary key)
org_name
org_type
org_acronym

From everything I’ve read, I’ve set up my table and relationships correctly with the primary and foreign keys (ind_cst_key=cst_ind_key & org_cst_key=cst_org_key) and cascading updates and deletions, but I can’t wrap my head around setting up a data entry form correctly so that all forms are updated seamlessly.

I understand that the relationship is just a constraint, dictating what can and cannot be entered into the child table, and not proactive in terms of creating a record on the child when one is created on the parent. My question is how do I set up a data entry form where a user doesn’t have to mess with knowing and entering keys to update all the tables.

When I create a new record in the co_individuals table, how do I set up my tables so that a corresponding record is created on the co_customer table? Currently, I enter a new record on co_individuals and nothing updates in the co_customers table. The same situation exists between co_organization and co_customer.

Thanks for any help!

[andersonEE]

Your junction table (co_customer) is where you build your relationship between individuals and organizations. Adding an individual does not automatically make him a customer...you need to add an entry in the co_customer table linking him to an organization for that to happen.

I think there is a way to have one form where you can add a new customer and also link the customer to an organization all at once, but I am not able to try it right now. I think maybe multiple forms? Perhaps someone else can help here.

[jhawk]

Thanks for the reply, andersonEE. I was beginning to wonder if anyone could help me with my issue.

I'll see if I can give multiple forms a try to see what I come up with.

Thanks for the idea.