UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> How To I Restructure My Table For New Requirement?, Access 2016    
 
   
momo2000
post Sep 10 2019, 08:56 PM
Post#1



Posts: 55
Joined: 6-November 13



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?
Go to the top of the page
 
RJD
post Sep 10 2019, 09:07 PM
Post#2


UtterAccess VIP
Posts: 10,081
Joined: 25-October 10
From: Gulf South USA


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

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
MadPiet
post Sep 10 2019, 10:44 PM
Post#3



Posts: 3,331
Joined: 27-February 09



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.
Go to the top of the page
 
momo2000
post Sep 12 2019, 09:27 AM
Post#4



Posts: 55
Joined: 6-November 13



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!
Go to the top of the page
 
MadPiet
post Sep 12 2019, 09:45 AM
Post#5



Posts: 3,331
Joined: 27-February 09



Yes.

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

So "serves" becomes (CounselorID*, TroopID*, etc) and the two IDs together make up the primary key.
Go to the top of the page
 
RJD
post Sep 12 2019, 04:26 PM
Post#6


UtterAccess VIP
Posts: 10,081
Joined: 25-October 10
From: Gulf South USA


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)
Attached File  CounselorsAndTroops.zip ( 27.61K )Number of downloads: 9
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
momo2000
post Sep 17 2019, 07:21 PM
Post#7



Posts: 55
Joined: 6-November 13



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 wink.gif
Go to the top of the page
 
RJD
post Sep 17 2019, 10:13 PM
Post#8


UtterAccess VIP
Posts: 10,081
Joined: 25-October 10
From: Gulf South USA


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

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    17th October 2019 - 02:55 AM