Data entry form with three junction tables?

[justgeig]

I've got the following table structure (well, this is a small part of it):




And I setup a nice data entry form that looks like this:


Is what I'm trying to do possible with there being junction tables in between? Whenever I've used subforms in junction tables before, I've always based it on the junction table itself with a combo box and then had a button to create a new entry in a separate form based on the child. After the new entry was created, then the user was able to select it in the combo box. I'd REALLY like to simplify that process to speed up data entry but after making this form the way I did, I've discovered that I haven't been able to figure out how to make it happen :-( Suggestions?

[June7]

Can have subforms up to, I think, 7 levels, but only the last can be in Continuous or Datasheet.

Here is another way to link subforms: http://www.fmsinc.com/MicrosoftAcces...edSubforms.asp

[xeifohShoh2]

Hello,

As per your requirement, and the images that you have given. It seems to have a tree like structure. For that you need to create several tables with perfect interlinking between the elements at proper heirarchy.

If you need, and if you can provide me details, then I can make the table architecture for you.

Let me know If I can be of some help.

Regards

[ssanfu]

I've been looking at your relationship image and it looks to me you shouldn't have any many to many relationships at all.

One company can have many emails
One email goes to one company
therefore relationship is one company to many emails

One company can have many phone numbers
One phone number goes to one company
therefore relationship is one company to many phone numbers

One company can have many addresses
One address goes to one company
therefore relationship is one company to many addresses

(I like to put the one table on the left... easier for me to see the flow..)
Maybe this would work for you....
Attachment 13410

The main form detail section would be the main company data
The footer would have the 3 sub forms...very simple

[justgeig]

Apologies, I didn't include the entire table structure to make it more clear but I probably made it less clear. Here is the entire structure for customer data.

[ssanfu]

I still contend that there is only one many to many table. All the rest are one to many...

Attachment 13431


This still applies:

One company can have many emails
One email goes to one company
therefore relationship is one company to many emails

One company can have many phone numbers
One phone number goes to one company
therefore relationship is one company to many phone numbers

One company can have many addresses
One address goes to one company
therefore relationship is one company to many addresses

--------------------------------

One contact can have many emails
One email goes to one contact
therefore relationship is one contact to many emails

One contact can have many phone numbers
One phone number goes to one contact
therefore relationship is one contact to many phone numbers

One contact can have many addresses
One address goes to one contact
therefore relationship is one contact to many addresses

-------------------------------------------------------
One contact can work for many companies
One company can have many contacts
therefore relationship is many to many



I realize I don't know your process or requirements, but I think this gets to the same place... and less complicated.

My $0.02....