It probably would have been smarter if I had a separate field for each of the 16 units but I didn’t do it that way . . . so each of the “srvUnitxx” contain the same possible unit ID’s in a dropdown.
It would not. What you have now is not ideal. Using 16 different fields would be worse, not better.
A related "join" table linking members to units would be the best approach. Then, you could generate this report from a layout based on this join table and this process would become much simpler.
I suggest that you export just the Member ID and one of the three SrvUnitXX fields to a new (not a temporary) table 3 times. I'll call this new table: Member_Unit. You can then link this table to Members and Units like this:
Members::__pkMemberID = Member_Unit::_fkMemberUnit
Units::__pkUnitID = Member_Unit::_fkUnitID
Not knowing the field names, I've named them as I would name them. Use your fields in place of mine.
From here, you can set up a list view layout that includes fields as needed from Members to put info into the body and include fields from Units as a sub summary part's sub header or as fields in a header part--depending on how you want to set up this report.
You can either sort your records in this table by Unit (and put the unit fields in the sub summary part) or you can peform a find for one unit at a time (and then the fields can be placed in the header) to generate a separate report document for each company.
And if you specfiy a "page break before every occurrence" of the sub summary part, you can print the list of members for each unit on a separate page if using the first option.
Note that you can now retrofit your existing design to use this new join table in place of your original set of three srvUnitXX fields.
When you say "each unit has a director", I take that to mean that the director is just an attribute of units, which means when you say "I need to be able to publish a list for each of the 16 directors." this is exactly the same as "I need to be able to publish a list for each of the 16 units."... right? Basically, I'm ignoring all this information about the director...
So currently you have 3 fields in members which allow you to insert a relationship key for units.
You could improve this by having a 3rd table which is a "join table". Read up on join tables and many-to-many relationships. Then you wouldn't have the limit of 3.
But to solve your problem, all you need is a calculation field in members that is:
srvUnit01 & "¶" & srvUnit02 & "¶" & srvUnit03
Then make a new relationship between your tables based on this new field. This is called a "multi-key". The relationship will match whenever one or more of the return separated lines matches.
PhilModJunk and Jason Wood- Thank you both. I will take a run at each suggestion tonight and let you know how I fared. And Jason, yes you are correct . . . the directors are not in the table but all I will need to do is print a list for each unit and hand it off to each of them. Thanks.
Note that you cannot do that with Jason's method.
1 of 1 people found this helpful
To clarify, my "quick" method, which does not require a 3rd table (even though I suggest this is the better method in the long run), means that you cannot run this as a single report, which lists all the units and then all the members for each under them.
But, you can run a separate report for each unit. You simply GTRR from units to members, through the multi-key field relationship, and you've got all the members for that unit. Caveat: if you relate back to units to get the unit name (for a header, for example), you might get the wrong unit (if the first record member is linked to multiple units), so you'd want to put the unit name into a global variable first, and put that on the layout, which adds new problems if you want to display multiple reports in different windows at the same time.
So yeah, a join table has many advantages, is definitely the most correct method, and you should do it. But if you just need to quickly get these reports made, you can get my method to work in a hurry.
You have perfectly illustrated the pro's and cons of setting up a many to many relationship using MultiValues instead of a join table.