1 Reply Latest reply on Dec 27, 2010 8:16 AM by philmodjunk

    Help sorting

    ArtKanowitz

      Title

      Help sorting

      Post

      I have a Medical Director Registration Database. Each MD has a record with multiple fields containing demographic items. One of the fields is their practice region. However, some of the MDs practice in more than one region so I have three separate fields for their practice regions (region1, region2, region3). Now I want to do a sort however I want to combine all the regions is a single sort.

      Example:
      MD_AAA     North

      MD BBB     North, West, South

      MD_CCC  South

      MD_DDD  West, South

      I want sort to look like this:

      North  Region     

            MD_AAA
            MD_BBB

      West Region

        MD_BBB
        MD_DDD

      South Region

        MD_BBB
        MD_CCC
        MD_DDD

      How do I create such a sort??

      Thanks

      Arthur Kanowitz MD

        • 1. Re: Help sorting
          philmodjunk

          You'll need to restructure your database or design a different layout to achieve the same purpose. Here's one way you might achieve this:

          Define a related table, Regions with at least these two fields:

          MD_ID  (Number)
          Region (Text, with values North, West, South, etc.)

          Relate it to your original table, (I'll call it Doctors) like this:

          Doctors::MD_ID = Regions::MD_ID    (Enable allow creation of records via this relationship for Regions, but NOT for Doctors.)

          Doctors::MD_ID should be an auto-entered serial number.

          Place a portal to Regions on your layout. Place Regions::Region inside this portal and format it as a drop down with a value list listing the possible regions.

          Now you can assign a doctor to multiple regions by selecting different regions in successive rows in the portal.

          To get the listing you specify, create a list view, summary report based on the Regions table. You can add info from the related Doctors record by adding fields from the Doctors table to your layout.

          If you don't want your regions listed in alpabetical order, you can speciy the same order as order of your regions in your value list to get an arbitrary order.