DB structure best practice question

[HansV]

I think the tblConRole table isn't needed any more, since a contact's role depends on the team the contact is assigned to.
So I'd structure it like this:

S0460.png

There is a Location field in tblContacts and also in tblTeams. Are they related? Do you need both?
There shouldn't be a Program field in tblTeams, I think, since you have a junction table tblPrgTm that defines a many-to-many relationship between tblTeams and tblProg

[NWGal]

I think the tblConRole table isn't needed any more, since a contact's role depends on the team the contact is assigned to.
So I'd structure it like this:

S0460.png

There is a Location field in tblContacts and also in tblTeams. Are they related? Do you need both?
There shouldn't be a Program field in tblTeams, I think, since you have a junction table tblPrgTm that defines a many-to-many relationship between tblTeams and tblProg

Ok, first the roles. I think I understand what you are doing, but am still unsure how to keep a person attached to his or her role independent of assignment. What I mean is a contact who is a volunteer, can and will be serving as a volunteer on many teams, but may also be the individual, the person who a team is for. So Yogi is assigned to my location ( and only my location) as a volunteer. He serves in this role on 1 Wellness team, 2 ABLE teams, and 1 TSP team. At the same time, he himself has wellness team in which another volunteer is his coach and he is the person who that team is for. I need to be able to have a report of all of my volunteers, as volunteers and/or staff, and yet also have report that shows all active teams, by program, with their members, by role.

As for locations, we have several locations, but we don't share personnel or individuals we support between the locations. We have the same programs in each location. So locations are a single entity on the one side of assignment and/or role relationships.

In a nutshell here is our structure:
Organization - 4 programs in each of several locations
Staff
Location 1, 2, 3, 4...
Assigned:
Staff
Volunteers
Individuals (who live in that county)
All programs
multiple teams made up of staff/volunteers & 1 individual
Classes/Training program specific,
Location - can be in any location
Participants - open to all members from all locations

[HansV]

I'm confused now. A contact can be a volunteer for many teams, and have another role in other teams, but a contact should also have the role of volunteer independently of any team? If so, how many roles can a contact have that are NOT related to a team?

[NWGal]

I'm confused now. A contact can be a volunteer for many teams, and have another role in other teams, but a contact should also have the role of volunteer independently of any team? If so, how many roles can a contact have that are NOT related to a team?

Sorry I didn't mean to be so confusing. Contacts do have roles apart from assignments, e.g a volunteer is a volunteer regardless of his or her assignments, staff is always staff and so on. This is why I set it up to select a contacts roles when they are first entered into the db.

[HansV]

Can a contact have more than one role that is *not* linked to an assignment?

[NWGal]

Can a contact have more than one role that is *not* linked to an assignment?

Yes

[HansV]

In the attached version, I have created a union query to display all contacts who have RoleID = 2 or 3 either through an assignment or independently of assignments.
I had to change some of the ID fields from text to number in order to create relationships with enforced referential integrity.

ABLEzipHV.zip

[NWGal]

In the attached version, I have created a union query to display all contacts who have RoleID = 2 or 3 either through an assignment or independently of assignments.
I had to change some of the ID fields from text to number in order to create relationships with enforced referential integrity.

ABLEzipHV.zip

I thought it was working, but when I add a new team, the new records show up in the assignment table without role ids.

[HansV]

The subforms sbfrmInd and sbfrmVol of frmNewTeam only populate the ConID and TeamID fields. To make them fill the RoleID field too, you have to do the following:

Make RoleID visible in qryInd and qryVol.
Change the Column Count property of the combo box on both subforms from 2 to 3 (to accommodate the RoleID column).
Add a hidden text box bound to RoleID to both subforms and populate it in the After Update event of the combo box:

Code: Select all

Private Sub ConId_AfterUpdate()
    Me.RoleID = Me.ConID.Column(2)
End Sub

See the attached version.

ABLEzipHV.zip

[NWGal]

The subforms sbfrmInd and sbfrmVol of frmNewTeam only populate the ConID and TeamID fields. To make them fill the RoleID field too, you have to do the following:

Make RoleID visible in qryInd and qryVol.
Change the Column Count property of the combo box on both subforms from 2 to 3 (to accommodate the RoleID column).
Add a hidden text box bound to RoleID to both subforms and populate it in the After Update event of the combo box:

Code: Select all

Private Sub ConId_AfterUpdate()
    Me.RoleID = Me.ConID.Column(2)
End Sub

See the attached version.

ABLEzipHV.zip

AWESOME!! Thank you!

[NWGal]

Now I'm working on building the report - since at the report level I need to group and sort by program, then by volunteer, then by teams that volunteer leads including the start date, what is the best structure in the queries to accomplish this? I can extend the qryInd and qryVol to include the team data, and that sorts out the roles, but leaves me with two separate queries to bring together my report.

[HansV]

I'm not sure I understand fully what you want, and I still have doubts about the setup, but in the attached version I have created a query qryProgs and two slightly different reports based on this query.

ABLEzipHV.zip

[NWGal]

I'm not sure I understand fully what you want, and I still have doubts about the setup, but in the attached version I have created a query qryProgs and two slightly different reports based on this query.

ABLEzipHV.zip

Well, if you have doubts, then so do I. I really want to set this up in the "rightest" way possible, so that going forward I can avoid problems for my end users. I'm wondering if I need to back up, and rethink this again. Bear with me please, and know that I really, really appreciate your help.
If I don't assign roles at the main contact level, but I do from the assignment form, then I still end up with a block of data that contains all the pieces needed to query out, for example, all volunteers, or all individuals? I'm thinking the assignment form would then include combo boxes for all the variables right?

[HansV]

Did you take a look at the queries and report?

[NWGal]

Did you take a look at the queries and report?

Yes! Those look good. I just was thinking about your concerns and wondering, as I mentioned if I needed to rethink my structure. Of course I'd rather not if what I have works and will continue to serve us well.

[HansV]

I would recommend removing the Program field from tblTeams - the relationship between programs and teams is defined by the junction table tblProgTm.
This would mean you'd have to replace the Program combo box on frmNewTeam with a subform based on tblProgTm.

[NWGal]

I would recommend removing the Program field from tblTeams - the relationship between programs and teams is defined by the junction table tblProgTm.
This would mean you'd have to replace the Program combo box on frmNewTeam with a subform based on tblProgTm.

A subform with a combo box on it for choosing program?

[HansV]

Yes, indeed. That is, if you really have a many-to-many relationship between teams and programs.

[NWGal]

Yes, indeed. That is, if you really have a many-to-many relationship between teams and programs.

Many teams, many programs? Yes. I'll give that a try Thanks

[NWGal]

Yes, indeed. That is, if you really have a many-to-many relationship between teams and programs.

Many teams, many programs? Yes. I'll give that a try Thanks

Do you have time and patience for one more challenge? I will need to figure out how to determine a stipend total. Each volunteer gets a small re-reimbursement for expenses for each program he has a team in. It's the same amount each time, a fixed $ amount, but it not based on number of teams, only whether he or she has an assignment in that program. A person could have 2 assignments in one program, 1 in another and none in the 3rd, so he would get the stipend amount x2, and if he gets an assignment in a third program, it would change to x3. Soooo... somewhere, a query I'm sure, I need to use an IIf statement right? Or another function like count? To check if there is at least 1 record for each volunteer, in each program, and for each program, return the stipend amount. Then in the report I would use a sum/total field to total the 1, 2, 3 or 4 stipend amounts. Am I one the right track?