6 Replies Latest reply on Mar 14, 2017 9:12 AM by philmodjunk

    Help creating temporary table

    DavidRegenthal

      Like many people, it’s difficult for me when I have to expose my ignorance.  Permit me to apologize in advance of my question as I suspect the solution is probably pretty basic . . . but I just can’t get my head around it.  I would certainly appreciate anyone pointing me in the right direction, and thanks to all in advance of any knowledge.  Here’s my task:

       

      I have a functioning database with 2000+ members.

      There are multiple (16) units, (companies) that my members may have worked in over time.

      Most members have only worked for one unit (company).

      A fair number of members have worked in two units (companies).

      A very small number of members have worked in three units (companies).

      Nobody has worked in more than 3.

       

      Each unit has a director.

      The directors want an alpha listing of everyone who has worked in their unit (whether they are still in that unit or not).

      So, I need to be able to publish a list for each of the 16 directors.

       

      The fields I am concerned with are as follows:

       

      srvUnit01

      srvUnit02

      srvUnit03

       

      FirstName

      LastName

       

      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.

       

      What I’m trying to do (I think, unless there is a better way) is to figure out how to read my database into a temporary table three different times (one for each srvUnit) so that I will have everyone in and can then do a sort by srvUnit, then alpha by last then first name.

       

      Gawd I hope this makes sense . . . you can see I’m not very good at this.

      Can anyone provide a little guidance or advise if there is a toot anywhere than might be useful?

        • 1. Re: Help creating temporary table
          philmodjunk

          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---<Member_Unit>-----Units

          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.

          • 2. Re: Help creating temporary table
            Jason Wood

            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.

            • 3. Re: Help creating temporary table
              DavidRegenthal

              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.

              • 4. Re: Help creating temporary table
                BruceRobertson

                Note that you cannot do that with Jason's method.

                • 5. Re: Help creating temporary table
                  Jason Wood

                  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.

                  1 of 1 people found this helpful
                  • 6. Re: Help creating temporary table
                    philmodjunk

                    Hi Jason,

                    You have perfectly illustrated the pro's and cons of setting up a many to many relationship using MultiValues instead of a join table.