DB structure best practice question

[NWGal]

Currently I have a separate table for 4 variables that a contact can be "assigned" to in our organization - role (many possible), funding (one), location (one), and program (many). I have set up a junction table for each of these and that works well, but as I was working on a form for input, I realized I could have one junction table for all four of these tables. Is that sensible? I mean, is there any inherent flaw in that design that would cause me trouble down the road.
If I have one junction table, instead of using multiple subforms on my main form, I could use just one subform with combo boxes for each of those variables that need to be set right?

[HansV]

If only one funding and one location can be assigned to a contact, you don't need junction tables for those - the contacts table can contain a funding field and a location field. On the form(s) bound to the contacts table, use combo boxes bound to these fields.

I assume that the roles and programs of a contact are independent of each other. Therefore, you need two separate junction tables for these, and two separate subforms bound to these junction tables.

[NWGal]

If only one funding and one location can be assigned to a contact, you don't need junction tables for those - the contacts table can contain a funding field and a location field. On the form(s) bound to the contacts table, use combo boxes bound to these fields.

I assume that the roles and programs of a contact are independent of each other. Therefore, you need two separate junction tables for these, and two separate subforms bound to these junction tables.

Ok, that makes sense. Thanks

[NWGal]

Next question...to match people with assignments where an assignment may have more then one person, should I treat assignments as the one side of relationship and build a form/subform (like my original contact form) where I enter the basic data of the assignment i.e. id#, program, location, start date, and then choose all contacts for that assignment from a combo on subform, or even with chk boxes like I did with roles? OR, is it best to come at this from the people side, i.e. continue on my Contact form with a subform for entering assignments. The first way seems to me to have the advantage of being able to assign several people with one whack through the checklist or combo, where as with the latter way, if I was assigning more than one person, I'd have to go to each persons record.
Whew, I hope that makes sense.

[HansV]

Why not offer both?

When you are creating or editing an assignment, it is convenient to use a main form based on the assignment table, with a subform to select/deselect contacts.
When you are creating or editing a contact, it is more convenient to use a main form based on the contact table, with a subform to select/deselect assignments.

[NWGal]

Why not offer both?

When you are creating or editing an assignment, it is convenient to use a main form based on the assignment table, with a subform to select/deselect contacts.
When you are creating or editing a contact, it is more convenient to use a main form based on the contact table, with a subform to select/deselect assignments.

That's a great idea! I'll take a whack at this and let you know if I get it working. :)

[NWGal]

Why not offer both?

When you are creating or editing an assignment, it is convenient to use a main form based on the assignment table, with a subform to select/deselect contacts.
When you are creating or editing a contact, it is more convenient to use a main form based on the contact table, with a subform to select/deselect assignments.

That's a great idea! I'll take a whack at this and let you know if I get it working. :)

Arggh...now I am having trouble figuring out structure for this. Could be my brain is tired. I have (main) frmAsgn based on assignment table and sbfrmContact which has a multiselect listbox on it getting values from qryContact (so that I can later filter out archived contacts). Does that sound right. It's not working but again, my brain is tired and I may be missing something. I'm not sure what if anything the subform should be based on. Sorry to sound so dense.

[HansV]

The easiest way is to create a continuous (or datasheet) subform bound to the junction table or to a query based on the junction table.
The subform would be linked to the main form by AssignmentID (or whatever you call the primary key of the assignment table, and it would contain a combo box bound to the ContactID field (or whatever you call the primary key of the contact table).
This setup doesn't require VBA code, at least not for its basic operation.

As an alternative to a subform, you could use a multi-select list box directly on the main form itself. The list box would be unbound, and its Row Source would be the contact table or a query based on the contact table. VBA code is needed to add records to the junction table or remove them when items in the list box are selected/deselected.

The attached sample database demonstrates both approaches in a students/classes setting.

Enrollment.zip

[NWGal]

The easiest way is to create a continuous (or datasheet) subform bound to the junction table or to a query based on the junction table.
The subform would be linked to the main form by AssignmentID (or whatever you call the primary key of the assignment table, and it would contain a combo box bound to the ContactID field (or whatever you call the primary key of the contact table).
This setup doesn't require VBA code, at least not for its basic operation.

As an alternative to a subform, you could use a multi-select list box directly on the main form itself. The list box would be unbound, and its Row Source would be the contact table or a query based on the contact table. VBA code is needed to add records to the junction table or remove them when items in the list box are selected/deselected.

The attached sample database demonstrates both approaches in a students/classes setting.

Enrollment.zip

Hi,
I know it's been a little bit since this post, but I just got back to working on this again. I am trying the subform route, and I can see it will work fine, however, if I want to also include the roleId in the assignment junction table, how do I get that piece. The reason I need to do this is that although every person can have several roles, when it comes to my assignments, for reporting I need to break out the assignment by roles. It would be the equivalent of the Student class sample needing to have a report which separated out boys and girls, if that made sense.
Thanks

[HansV]

Are roles linked to assignments? For example, contact A has role K in connection with assignment V and roles L and M in connection with assignment W? Or are roles and assignments independent of each other.

[NWGal]

Are roles linked to assignments? For example, contact A has role K in connection with assignment V and roles L and M in connection with assignment W? Or are roles and assignments independent of each other.

Yes, on an assignment the person can be in one role or the other. So A can definitely be in Role K, on one assignment, but Role L on another.

[NWGal]

Are roles linked to assignments? For example, contact A has role K in connection with assignment V and roles L and M in connection with assignment W? Or are roles and assignments independent of each other.

Yes, on an assignment the person can be in one role or the other. So A can definitely be in Role K, on one assignment, but Role L on another.

I should add that I have queries made to look at contacts by roles.

[HansV]

Can a contact have more than one role for one assignment, or just one?

[NWGal]

Can a contact have more than one role for one assignment, or just one?

No, when a contact gets an assignment he or she has a specific role on that assignment. This is one reason I chose to have assignments be the one side of a one to many relationship.

[HansV]

OK, then you can add a field RoleID to the contacts-assignments junction table, and create a combo box bound to this field on the subform, with the Roles table as Row Source.

[NWGal]

OK, then you can add a field RoleID to the contacts-assignments junction table, and create a combo box bound to this field on the subform, with the Roles table as Row Source.

Ok, is there a way to have the role that is already assigned to the contact be a part of the contact combo, so that I don't need to have an extra step. I've already taken the step of actually having two combo boxes, one for role A (always only one contact), and one for role B, can be one, or two people. Each combo gets it's data from it's respective query. Hope that makes sense.
Edited to add - all 2 or 3 contacts are showing up in the junction table, so that is all good :)

[HansV]

So is your problem solved now?

[NWGal]

So is your problem solved now?

No, because I have the contactids showing up, but not with their roleId

[HansV]

Could you post a sample database (zipped)? I have to admit that I can't visualize the current situation any more.

[NWGal]

Here you go, I saved as a dummy db and deleted fields in my contacts table with identifying info, so don't be alarmed by the name errors in the form.

ABLEzip.zip