Trouble with form to add new record data into a junction table

[graccess]

I have three tables, a company info table, a notes table and a junction table that consists of two foreign keys, one from the company info table and the other from the notes table. I have the junction table to establish the many to many relationship between company and notes. I also have a form to enter the new notes. What is the best way to get the related data into the junction table? VBA code, query or separate form to combine that data?

thx!

[June7]

Options:

1. one form bound to junction table with two comboboxes to select company and note

2. main form bound to company table and subform bound to junction table with a combobox to select note

3. main form bound to notes table and subform bound to junction table with a combobox to select company

Are there other fields in the junction table, like DateEnter, WhoEnter?

[graccess]

I only used 3 fields, the two foreign keys and a key for the junction table.

[June7]

Ok, issue is resolved?

[qa12dx]

...i think;
junction table not really required here. i use it in programing, access does that for you, it's to do with running codes effeciently and used in massive data volume.

use autonum field in co table to reference in the notes table.
so, when you have a new note, it will store the co.autonum in notes.num
in query join co.autonum to notes.num

[graccess]

That's also an interesting solution. I was leaning on the subform combobox suggestion, option 3 from june7 but now I'm not sure. The problem in facing though is how to reconcile the many to many relationship then. The db is getting designed to handle hundreds of companies with some multiple of notes assigned to each. Each note could have several companies affiliated with it. As I input the data I'll need the ability to assign more companies. Not sure which method would be more efficient.

[June7]

What is the Notes table? Is this a lookup table of standard note items? If yes, then this is a many-to-many relationship and junction table is required.

However, if there is no standard set of note items that users must select from and the 'note' is really a free-form text entry, no junction table is needed.

[graccess]

What is the Notes table? Is this a lookup table of standard note items? If yes, then this is a many-to-many relationship and junction table is required.

However, if there is no standard set of note items that users must select from and the 'note' is really a free-form text entry, no junction table is needed.

The Notes table is not a free form text entry. There are a half dozen text fields, but there's also an attachment field, a date set field for recording the day the note was created, a yes/no field and several items with multiple options to choose from that I will list within the form. Seems that the junction table with the subform option gives me the most flexibility.

[graccess]

Regarding the bounded main form to my notes table suggested earlier. Shouldn't the notes form be bound to a query which contains the junction table?
Thanks again!

[June7]

No. A form can be used to enter/edit records for only a single table.

If this is truly a many-to-many relationship, the form options are described in post2. Sounds like option 2 would be most suited to your situation.

[graccess]

What's your approach to the table properties in the junction table, two primary keys (one for each table) or a unique primary key with the two foreign keys? Does it matter?

[June7]

I avoid compound primary keys. However, unless the primary key is used as foreign key in another table, doesn't matter. Do want to prevent duplicate company/note pairs so at least set the two fields as compound index. Designating the pair as a compound primary key is one way to get that done.

[graccess]

I avoid compound primary keys. However, unless the primary key is used as foreign key in another table, doesn't matter. Do want to prevent duplicate company/note pairs so at least set the two fields as compound index. Designating the pair as a compound primary key is one way to get that done.

Working through the Junction table issue has led me to also create a couple additional tables that I believe also have many to many relationships with each other(Events table and an Attachments table). Does it make sense to create a junction table for each pair of m2m tables or create one master junction table and combine all four interrelated tables? I realize there will be times that for a given record within this master junction table a relationship may not exist between one or two of the other tables(the junction fields), does that matter? Having some trouble conceptualizing this in my head. Appreciate your thoughts on how you would or have handled a situation like this in the past.

[June7]

Do Events or Attachments relate to Company or Notes?

[graccess]

Yes, they both do. But not everytime that Company relates to notes, an attachment or event will relate to each other and vice versa however in some records all four will relate. Other times a given record could have company and event relating but not notes. I could create a Junction for each and every table pair but I'm concerned about how I'll later on in the development handle a complicated query that will need to return the complete relationship in a report. Am I thinking of this correctly?