[X]My Assistant

[momo2000]

I’ve run a Boy Scout Merit Badge Counselor program for about 5 years with Access, and many of you have helped me along the way. No real problems for the past two years, but now I have one, and I’m not sure how to proceed.

In its simplest form, I have two main tables: Troops and Adults. They are associated by selecting a field in the Adult table that is a lookup in the Troops table. I have a nice form that I use for data entry/updates. I have a nicely formatted report that shows the Adults (and their details) for each Troop. So, then I have some VBA code that I wrote loops through the Troops table and prints (to a PDF) that report (specific for that Troop), and emails out the report to each Troop’s contact. Works well.

Now the problem=>Traditionally, each Adult Merit Badge Counselor has only been associated with EXACTLY one Troop. However, with the new BSA structure, a Merit Badge Counselor can be associated with more than one Troop, and I am struggling with how to change my table structure to accommodate this.

If I make the “troop indicator field” in the Adult table a multivalue field, that causes problems and doesn’t seem to work, and the Report and code all fail. Additionally, I recall that mutlivalue fields aren't a preferred way to do things, and can cause other problems. Any suggestions on how to think about organize something like this?

[RJD]

Hi: I can't be certain without seeing your whole db design, but it sounds like you need a junction table between Adults and Troops. Adults and Troops tables would stand alone, not linked. The junction table (say, tblAdultTroops) would contain the relationships between Adults and Troops - and then many Adults could be assigned to a Troop and many Troops could be assigned to an Adult.

I don't know how much restructuring would be required in your db, but if you want a many-many relationship, a junction table is a solution.

And a big NO, my advice is NOT to use a MVF.

HTH
Joe

[MadPiet]

Now the problem=>Traditionally, each Adult Merit Badge Counselor has only been associated with EXACTLY one Troop. However, with the new BSA structure, a Merit Badge Counselor can be associated with more than one Troop, and I am struggling with how to change my table structure to accommodate this.

If a Counselor can be associated with more than one Troop, but each Troop has only one Counselor, than the CounselorID goes in the Troop table.

Can a Troop have more than one Counselor, or only one? If More than one, then (TroopID, CounselorID) becomes a junction table. If not, CounselorID gets added to the Troop table as a foreign key.

[momo2000]

Hey sorry, looks like you guys responded right away, but I never got an email alert, so I assumed no one did, until I manually checked in just now, so sorry for the delay.

RJD: I’m not familiar with junction tables, but will do some reading on them and see if that will work.

MadPiet: Sorry maybe I didn’t explain well. A troop has always been able to have multiple counselors. BUT NOW ALSO a counselor can be assigned to multiple troops. Sounds like junction table again.

OK, I’m off to read about junction tables and will report back!

[MadPiet]

Yes.

Counselor---(1,M)--serves--(M,1)---Troop

So "serves" becomes (CounselorID*, TroopID*, etc) and the two IDs together make up the primary key.

[RJD]

Hi again: I though maybe a small starting demo might be helpful to learning about junction tables. See the table list and contents, and see the form with its subform that creates the junction table records. This is just a starter, using your adult/troop assignment concept. You can also reverse this process and assign adults to troops (troop main form and adult assignments subform. And, of course, you will need entry forms for new or modified adults and troops. Plus whatever reports are required. I suspect you already have most of this, but these will need to be changed to accommodate the new junction table design.

See if this design demo helps ...

HTH
Joe

Attached File(s)

 CounselorsAndTroops.zip ( 27.61K )Number of downloads: 10

 

[momo2000]

RDJ: Thanks for the sample!!! I spent some time working with it tonight, really helps to have a tangible example like that. You are so kind to take the time to do this. I think with this I can redesign things. I think this will be a big undertaking as I have to redesign most everything (tables, input forms, reports, and VBA code). Unfortunately, I don't think I can piecemeal, I think I have to go all in

[RJD]

You are very welcome. Glad that was helpful.

Yes, I suspect this will require a large rework - but without seeing your db it is not really possible to know how much of one. However, reworking using the junction table, and associated other objects should open up more power and flexibility for you.

Good luck with your project.

Regards,
Joe