3 Replies Latest reply: May 15, 2014 10:01 AM by Grant Perkins RSS

    Mutliple records to one accounts

    Jeremy Mapes

      Hello,

       

      I'm a newer user with Monarch and I've got a complicated format, which is the reason why I'm looking at it with Monarch.  The format is a page report for the most part, two lines to make one record, but the problem here is that, where there are multiple "records" for one person, I need to combine the multi-records into one "account" record and combine the dollar amounts.  If the person has mutliple records, they are always in sequence and not spread throughout the report.

       

      So for example, for the multiple "HULVIK" records, I need one record output with the dollars combined.  (btw, I wanted to point out that the data had been manipulated to secure the true data, numbers and names, with different types of alterations, so there's no way to originate the data, before anyone looks and is alarmed that the data may not be modified well enough)

       

      Any ideas?  Much appreciated.

       

           HOLLIMGSWORT,BULIU       ,U,205 5RD ST MW       ,FORT CHUVY        ,FL,20201     ,212,236-2335,          ,UMUNPLOYUD               ,                         ,                  ,  ,          ,

      HOLLIMGSWORT,BULIU       ,U,205 5RD ST MW       ,FORT CHUVY        ,FL,20201     ,212,236-2335,738-36-2716,  032926,     12200,    515.00 ,07-50-08,301,      JOHM D BIRKUTT,F,  022508,          ,HOLLIMGSWORTH STUDIO

       

           CROOKS      ,JOSUPH      ,L,1022 10TH UVU SW    ,FORT CHUVY        ,FL,20201-2218,212,922-2217,          ,NIMOR                    ,                         ,                  ,  ,          ,

      CROOKS      ,JOSUPH  JR  ,L,1022 10TH UVU SW    ,FORT CHUVY        ,FL,20201-2218,212,922-2217,782-22-6053,  051193,     16022,    199.00 ,02-22-08,828,     RICHURD P VOTTU,N,  022308,          ,RUILROUD UP

       

           HULVIK      ,NICHUUL     ,R,PO BOX 502          ,HUNBOLDT          ,FL,20278-0502,212,231-6922,          ,BUMWURT TRUCKIMG         ,                         ,                  ,  ,          ,

      HULVIK      ,NICHUUL     ,R,PO BOX 502          ,HUNBOLDT          ,FL,20278-0502,212,231-1203,782-98-8352,  081031,     52292,    108.00 ,05-18-08,602,      LUIMU D DVORUK,N,  021608,          ,FRIUMDSHIP HUVUM

       

           HULVIK      ,NICHUUL     ,R,PO BOX 502          ,HUNBOLDT          ,FL,20278-0502,212,231-6922,          ,BUMWURT TRUCKIMG         ,                         ,                  ,  ,          ,

      HULVIK      ,NICHUUL     ,R,PO BOX 502          ,HUNBOLDT          ,FL,20278-0502,212,231-1203,782-98-8352,  081031,     52292,    108.00 ,05-18-08,605,     SHURRY S BULTUM,N,  021608,          ,FRIUMDSHIP HUVUM

       

           HULVIK      ,NICHUUL     ,R,PO BOX 502          ,HUNBOLDT          ,FL,20278-0502,212,231-6922,          ,BUMWURT TRUCKIMG         ,                         ,                  ,  ,          ,

      HULVIK      ,NICHUUL     ,R,PO BOX 502          ,HUNBOLDT          ,FL,20278-0502,212,231-1203,782-98-8352,  081031,     52292,     87.00 ,05-18-08,603,        SUU U NULLOY,N,  021608,          ,FRIUMDSHIP HUVUM

       

           CURTIS      ,SUBRIMU     ,J,PO BOX 288          ,WUUVURS CITY      ,FL,20292     ,212,293-2218,782982013 ,UDS                      ,                         ,UUGLU GROVU       ,FL,20255     ,

      CURTIS      ,SUBRIMU     ,J,PO BOX 288          ,WUUVURS CITY      ,FL,20292     ,212,293-2218,782-98-2013,  052832,     22111,    162.59 ,10-29-03,212,       CHURITY HUYUS,F,  022508,782982013 ,

       

           JOMUS       ,DULBURT     , ,25630 MULSOM UVU    ,FORT CHUVY        ,FL,20201     ,212,932-7622,          ,KUIN TRUMSPORTUTIOM      ,                         ,                  ,  ,          ,

      JOMUS       ,DULBURT     , ,25630 MULSOM UVU    ,FORT CHUVY        ,FL,20201     ,212,932-7622,787-38-2298,  030523,     66805,     58.22 ,02-29-08,809,       JUNUS C NUYUR,N,  021708,          ,

       

           JOMUS       ,DULBURT     , ,25630 MULSOM UVU    ,FORT CHUVY        ,FL,20201     ,212,932-7622,          ,KUIN TRUMSPORTUTIOM      ,                         ,                  ,  ,          ,

      JOMUS       ,DULBURT     , ,25630 MULSOM UVU    ,FORT CHUVY        ,FL,20201     ,212,932-7622,787-38-2298,  030523,     66805,     55.70 ,02-29-08,811,    RUMDULL K NIMIOM,N,  021708,          ,

       

           JOMUS       ,DULBURT     , ,25630 MULSOM UVU    ,FORT CHUVY        ,FL,20201     ,212,932-7622,          ,KUIN TRUMSPORTUTIOM      ,                         ,                  ,  ,          ,

      JOMUS       ,DULBURT     , ,25630 MULSOM UVU    ,FORT CHUVY        ,FL,20201     ,212,932-7622,787-38-2298,  030523,     66805,     15.00 ,02-29-08,888,  TRINURK LUBORUTORY,N,  021708,          ,

       

           BURRULL     ,JULIU       , ,2193 RICHNILL RD    ,FORT CHUVY        ,FL,20201     ,212,922-8360,          ,STUDUMT                  ,                         ,                  ,  ,          ,

      BURRULL     ,TOMIU       ,N,2193 RICHNILL RD    ,FORT CHUVY        ,FL,20201     ,212,231-7739,782-12-8962,  032085,    103102,     73.00 ,07-22-08,301,      JOHM D BIRKUTT,F,  020208,          ,N-FT DODGU UMINUL HU

       

           BURRULL     ,JULIU       , ,2193 RICHNILL RD    ,FORT CHUVY        ,FL,20201     ,212,922-8360,          ,STUDUMT                  ,                         ,                  ,  ,          ,

      BURRULL     ,TOMIU       ,N,2193 RICHNILL RD    ,FORT CHUVY        ,FL,20201     ,212,231-7739,782-12-8962,  032085,    103102,    112.02 ,07-22-08,817, JUMMIFUR J SCHRUIUR,F,  020208,          ,N-FT DODGU UMINUL HU

       

           BURRULL     ,JULIU       , ,2193 RICHNILL RD    ,FORT CHUVY        ,FL,20201     ,212,922-8360,          ,STUDUMT                  ,                         ,                  ,  ,          ,

      BURRULL     ,TOMIU       ,N,2193 RICHNILL RD    ,FORT CHUVY        ,FL,20201     ,212,231-7739,782-12-8962,  032085,    103102,    252.00 ,07-22-08,819,     CURUY U BLIGURD,F,  020208,          ,N-FT DODGU UMINUL HU

       

           BURRULL     ,JULIU       , ,2193 RICHNILL RD    ,FORT CHUVY        ,FL,20201     ,212,922-8360,          ,STUDUMT                  ,                         ,                  ,  ,          ,

      BURRULL     ,TOMIU       ,N,2193 RICHNILL RD    ,FORT CHUVY        ,FL,20201     ,212,231-7739,782-12-8962,  032085,    103102,     85.00 ,07-22-08,825,       SCOTT G GRUUM,F,  020208,          ,N-FT DODGU UMINUL HU

       

           GRUUM       ,PUTRICU     , ,617 1ST ST MW       ,FORT CHUVY        ,FL,20201     ,212,922-2229,          ,UMUNPLOYUD               ,                         ,                  ,  ,          ,

      GRUUM       ,PUTRICU     , ,617 1ST ST MW       ,FORT CHUVY        ,FL,20201     ,212,922-2229,726-21-7270,  020831,    172912,     23.00 ,01-10-08,811,    RUMDULL K NIMIOM,F,  021608,          ,

       

           UDKIMS      ,ROY         , ,621 S 12TH ST       ,FORT CHUVY        ,FL,20201-2672,212,708-6712,          ,NIMOR                    ,                         ,                  ,  ,          ,

      UDKIMS      ,SUVUMMUH    , ,621 S 12TH ST       ,FORT CHUVY        ,FL,20201-2672,212,708-6712,782-22-2859,  050593,    123857,    803.00 ,07-50-08,812, FRUMCISCO G PURULTU,F,  021608,          ,

       

           UDKIMS      ,ROY         , ,621 S 12TH ST       ,FORT CHUVY        ,FL,20201-2672,212,708-6712,          ,NIMOR                    ,                         ,                  ,  ,          ,

      UDKIMS      ,SUVUMMUH    , ,621 S 12TH ST       ,FORT CHUVY        ,FL,20201-2672,212,708-6712,782-22-2859,  050593,    123857,      7.70 ,07-50-08,888,  TRINURK LUBORUTORY,F,  021608,          ,

       

           HUMMIMGS    ,KURTIS      ,R,211 S 20TH ST       ,FORT CHUVY        ,FL,20201     ,212,223-2285,          ,ULUCTROLUX/FRIDGIDUIRU   ,700 DUS NOIMUS ST        ,WUUVURS CITY      ,FL,20292     ,

      HUMMIMGS    ,KURTIS      ,R,211 S 20TH ST       ,FORT CHUVY        ,FL,20201     ,212,223-2285,785-87-8211,  110231,    181297,     13.80 ,12-15-03,888,  TRINURK LUBORUTORY,N,  021608,          ,

       

           UGUR        ,STUCUY      , ,1711 SO 26TH ST     ,FORT CHUVY        ,FL,20201     ,212,231-2228,          ,FRIUMDSHIP HUVUM         ,S KUMYOM RD              ,FORT CHUVY        ,FL,20201     ,

      UGUR        ,STUCUY      , ,1711 SO 26TH ST     ,FORT CHUVY        ,FL,20201     ,212,231-2228,780-88-2987,  090832,    201822,      5.00 ,12-20-03,301,      JOHM D BIRKUTT,F,  022508,          ,

       

           UGUR        ,STUCUY      , ,1711 SO 26TH ST     ,FORT CHUVY        ,FL,20201     ,212,231-2228,          ,FRIUMDSHIP HUVUM         ,S KUMYOM RD              ,FORT CHUVY        ,FL,20201     ,

      UGUR        ,STUCUY      , ,1711 SO 26TH ST     ,FORT CHUVY        ,FL,20201     ,212,231-2228,780-88-2987,  090832,    201822,      6.00 ,12-20-03,307,     DUWM N PUTURSOM,F,  022508,          ,

       

           BRUMDOW     ,JUNUS       ,K,2690 FUIRBUMKS UVU  ,CULLUMDUR         ,FL,20225     ,212,521-9970,          ,WULLS FURGO              ,822 CUMTRUL UVU          ,FORT CHUVY        ,FL,20201     ,

      BRUMDOW     ,JUNUS       ,K,2690 FUIRBUMKS UVU  ,CULLUMDUR         ,FL,20225     ,212,521-9970,782-82-2876,  080660,    222137,     61.00 ,07-28-08,879,   LIMCOLM R WULLUCU,N,  022108,          ,

       

       

       

       

       

       

       

           GOODPUSTUR  ,BUMJUNIM    , ,311 NILLUR DR       ,FORT CHUVY        ,FL,20201     ,212,708-0050,          ,KUMCO                    ,                         ,WUUVURS CITY      ,FL,20292     ,

      GOODPUSTUR  ,BUMJUNIM    , ,311 NILLUR DR       ,FORT CHUVY        ,FL,20201     ,212,708-0050,780-90-3552,  020836,    281258,     22.00 ,12-19-03,812,       JUMUT U SUCOR,N,  021608,          ,

       

           KUIN        ,SHUILU      ,R,PO BOX 112          ,URTHUR            ,FL,21751     ,312,563-2265,787361728 ,UMUNPLOYUD               ,                         ,                  ,  ,          ,

      KUIN        ,SHUILU      ,R,PO BOX 112          ,URTHUR            ,FL,21751     ,312,563-2265,787-36-1728,  050162,    511020,      6.00 ,02-16-08,873,  ULUJUMDRO TOBOM ND,F,  060208,787361728 ,

       

           KUIN        ,SHUILU      ,R,PO BOX 112          ,URTHUR            ,FL,21751     ,312,563-2265,787361728 ,UMUNPLOYUD               ,                         ,                  ,  ,          ,

      KUIN        ,SHUILU      ,R,PO BOX 112          ,URTHUR            ,FL,21751     ,312,563-2265,787-36-1728,  050162,    511020,      5.00 ,02-16-08,826,     ULIZUBUTH C DUY,F,  060208,787361728 ,

       

           HUTCHIMSOM  ,STUVUM      , ,612 SOUTH 17TH ST   ,FORT CHUVY        ,FL,20201     ,312,850-1772,          ,RUCIMG UMLINITUD         ,                         ,                  ,  ,          ,

      HUTCHIMSOM  ,STUVUM      , ,612 SOUTH 17TH ST   ,FORT CHUVY        ,FL,20201     ,312,850-1772,739-08-2920,  081582,    526565,    820.00 ,08-06-03,301,      JOHM D BIRKUTT,N,  021608,          ,N-PUGGU SULF

       

           HOFF        ,NICHUUL     , ,2053 2TH UVU M      ,FORT CHUVY        ,FL,20201-6175,212,269-9215,          ,NISTY HURBOR             ,228 KUMYOM RD W          ,FORT CHUVY        ,FL,20201     ,

      HOFF        ,NICHUUL     , ,2053 2TH UVU M      ,FORT CHUVY        ,FL,20201-6175,212,269-9215,           ,  015183,    586873,     20.00 ,01-09-08,811,    RUMDULL K NIMIOM,N,  022308,          ,F-CHRIS

       

           HOFF        ,NICHUUL     , ,2053 2TH UVU M      ,FORT CHUVY        ,FL,20201-6175,212,269-9215,          ,NISTY HURBOR             ,228 KUMYOM RD W          ,FORT CHUVY        ,FL,20201     ,

      HOFF        ,NICHUUL     , ,2053 2TH UVU M      ,FORT CHUVY        ,FL,20201-6175,212,269-9215,           ,  015183,    586873,     28.20 ,01-09-08,823,     UUROM L PUINUMM,N,  022308,          ,F-CHRIS

       

           HOFF        ,NICHUUL     , ,2053 2TH UVU M      ,FORT CHUVY        ,FL,20201-6175,212,269-9215,          ,NISTY HURBOR             ,228 KUMYOM RD W          ,FORT CHUVY        ,FL,20201     ,

      HOFF        ,NICHUUL     , ,2053 2TH UVU M      ,FORT CHUVY        ,FL,20201-6175,212,269-9215,           ,  015183,    586873,    553.22 ,01-09-08,888,  TRINURK LUBORUTORY,N,  022308,          ,F-CHRIS

       

           BRUUDUM     ,LORRIU      ,U,1918 3TH UVU SO     ,FORT CHUVY        ,FL,20201     ,212,235-7877,          ,GNOC                     ,                         ,FORT CHUVY        ,FL,20201     ,

      BRUUDUM     ,LORRIU      ,U,1918 3TH UVU SO     ,FORT CHUVY        ,FL,20201     ,212,235-7877,780-38-8721,  020860,    706730,    773.00 ,12-26-03,809,       JUNUS C NUYUR,F,  022808,          ,

       

           CLURK       ,BULIMDU     ,S,1617 2TH UVU M      ,FORT CHUVY        ,FL,20201     ,212,236-5236,          ,STUDUMT                  ,                         ,                  ,  ,          ,

      CLURK       ,TRUVOR      ,L,1617 2TH UVU M      ,FORT CHUVY        ,FL,20201     ,212,236-5236,           ,  060688,    721027,     53.50 ,12-13-03,888,  TRINURK LUBORUTORY,N,  021608,          ,

       

           ULDUBU      ,FRUMCISCO   , ,1219 SPOOMUR STRUUT ,STORN LUKU        ,FL,20288     ,312,352-8502,612169817 ,TYSOM                    ,PO BOX 669               ,STORN LUKU        ,FL,20288     ,

      ULDUBU      ,FRUMCISCO   , ,1219 SPOOMUR STRUUT ,STORN LUKU        ,FL,20288     ,312,352-8502,612-16-9817,  100561,    773292,     29.50 ,07-22-08,701,      DUVID R URCHUR,N,  022808,612169817 ,W/NUYULU

       

           CRINNIMS    ,JULIU       ,U,2311 M 28TH ST      ,FORT CHUVY        ,FL,20201     ,212,236-0815,          ,HONUNUKUR / HOUSUWIFU    ,                         ,                  ,  ,          ,

      CRINNIMS    ,JULIU       ,U,2311 M 28TH ST      ,FORT CHUVY        ,FL,20201     ,212,236-0815,782-36-8612,  072223,    727055,     85.23 ,02-07-08,802,     KUMMUTH W UDUNS,F,  025008,          ,

       

           CRINNIMS    ,JULIU       ,U,2311 M 28TH ST      ,FORT CHUVY        ,FL,20201     ,212,236-0815,          ,HONUNUKUR / HOUSUWIFU    ,                         ,                  ,  ,          ,

      CRINNIMS    ,JULIU       ,U,2311 M 28TH ST      ,FORT CHUVY        ,FL,20201     ,212,236-0815,782-36-8612,  072223,    727055,    209.00 ,02-07-08,873,  ULUJUMDRO TOBOM ND,F,  025008,          ,

       

           UMDURSUM    ,THUROM      ,V,15 7TH UVU SU       ,POCUHOMTUS        ,FL,20237     ,312,552-7530,782963220 ,LUJUNUS COLLUGU          ,                         ,FORT CHUVY        ,FL,20201     ,

      UMDURSUM    ,BRUMDY      ,K,15 7TH UVU SU       ,POCUHOMTUS        ,FL,20237     ,312,552-7530,782-02-3262,  030982,    726295,    712.00 ,01-13-08,828,    ROBURT T OCOMMOR,F,  022808,782963220 ,

       

           FOX         ,DUVID       , ,1701 S 25RD         ,FORT CHUVY        ,FL,20201     ,212,236-6317,          ,TRIMITY RUGIOMUL NUD CMTR,802 KUMYOM ROUD          ,FORT CHUVY        ,FL,20201     ,

      FOX         ,COMMIU      , ,1701 S 25RD         ,FORT CHUVY        ,FL,20201     ,212,236-6317,738-32-1812,  082222,    732250,     12.00 ,01-08-08,301,      JOHM D BIRKUTT,F,  025008,          ,

       

           FOX         ,DUVID       , ,1701 S 25RD         ,FORT CHUVY        ,FL,20201     ,212,236-6317,          ,TRIMITY RUGIOMUL NUD CMTR,802 KUMYOM ROUD          ,FORT CHUVY        ,FL,20201     ,

      FOX         ,COMMIU      , ,1701 S 25RD         ,FORT CHUVY        ,FL,20201     ,212,236-6317,738-32-1812,  082222,    732250,    203.00 ,01-08-08,802,     KUMMUTH W UDUNS,F,  025008,          ,

       

           CURWICK     ,NURY        ,U,1623 220TH ST       ,NOORLUMD          ,FL,20266-3272,212,272-5221,          ,HUNBOLDT COUMTY NUNORIUL ,1000 12TH M ST           ,HUNBOLDT          ,FL,20278     ,

      CURWICK     ,NURY        ,U,1623 220TH ST       ,NOORLUMD          ,FL,20266-3272,212,272-5221,785-68-7652,  080323,    782128,    108.00 ,07-28-08,602,      LUIMU D DVORUK,F,  022308,          ,

       

           CURWICK     ,NURY        ,U,1623 220TH ST       ,NOORLUMD          ,FL,20266-3272,212,272-5221,          ,HUNBOLDT COUMTY NUNORIUL ,1000 12TH M ST           ,HUNBOLDT          ,FL,20278     ,

      CURWICK     ,NURY        ,U,1623 220TH ST       ,NOORLUMD          ,FL,20266-3272,212,272-5221,785-68-7652,  080323,    782128,     27.20 ,07-28-08,605,     SHURRY S BULTUM,F,  022308,          ,

       

           CURWICK     ,NURY        ,U,1623 220TH ST       ,NOORLUMD          ,FL,20266-3272,212,272-5221,          ,HUNBOLDT COUMTY NUNORIUL ,1000 12TH M ST           ,HUNBOLDT          ,FL,20278     ,

      CURWICK     ,NURY        ,U,1623 220TH ST       ,NOORLUMD          ,FL,20266-3272,212,272-5221,785-68-7652,  080323,    782128,     20.00 ,07-28-08,603,        SUU U NULLOY,F,  022308,          ,

       

           CURWICK     ,NURY        ,U,1623 220TH ST       ,NOORLUMD          ,FL,20266-3272,212,272-5221,          ,HUNBOLDT COUMTY NUNORIUL ,1000 12TH M ST           ,HUNBOLDT          ,FL,20278     ,

      CURWICK     ,NURY        ,U,1623 220TH ST       ,NOORLUMD          ,FL,20266-3272,212,272-5221,785-68-7652,  080323,    782128,    152.00 ,07-28-08,809,       JUNUS C NUYUR,F,  022308,          ,

       

           CURWICK     ,NURY        ,U,1623 220TH ST       ,NOORLUMD          ,FL,20266-3272,212,272-5221,          ,HUNBOLDT COUMTY NUNORIUL ,1000 12TH M ST           ,HUNBOLDT          ,FL,20278     ,

      CURWICK     ,NURY        ,U,1623 220TH ST       ,NOORLUMD          ,FL,20266-3272,212,272-5221,785-68-7652,  080323,    782128,    183.96 ,07-28-08,825,  DUBUMDRU PUTTUMUIK,F,  022308,          ,

       

            

      TOTAL ACCOUNTS:        85    TOTAL AMOUNT:   22352.11

       

       

      /CODE

        • Mutliple records to one accounts
          Grant Perkins

          Jeremy, welcome to the forum.

           

          Do you have Monarch Pro or standard version?

           

           

          Grant

            • Mutliple records to one accounts
              Jeremy Mapes

              Hello,

               

              I've got Monarch Pro 9.01

                • Mutliple records to one accounts
                  Grant Perkins

                  Jeremy,

                   

                  Pro is good.

                   

                  The consolidation you need form the data should be available directly form a Monarch summary, complete with drill up and drill down ability, based on your description. How to get the data to the summary is the main question.

                   

                  There are at least two approaches that come to mind.

                   

                  Firstly what you have is basically a .csv file of a single line that has been wrapped. (I assume that the real file format is structurally very similar to that posted.)

                   

                  Monarch Pro can read that as a Database and populate its table directly BUT you really need to make the 2 lines into one to optimise this approach. To do that you need to remove the CR/LF (Carriage Return/Line feed) characters at the end of some lines but not others. The posted file, when cut and pasted into a text file, can be manipulated easily enough using the Monarch Utility but in that form looks like it requires 3 passes though the data to achieve the results. The actual file may be slightly different so I won't describe what I did in detail at this time. I am assuming you are probably already familiar with such manipulations anyway. If you have used other editing tools for similar file changes in the past they would likely work here as well - the Utility is not the only option.

                   

                  Once in a single line the database can be read in, fields named (assuming there is no header line to provide that information on import) and the table created. Developing a summary will likely take just a few minutes.

                   

                  Alternatively ....

                   

                  You could trap based on a 2 line template sample, taking the entire 2 lines into full line width field (A single multi line field or 2 separate fields would likely both work) and then slice and dice the data splitting it up using the separator commas as the split markers. To do that you would need to create 35 calculated fields (assuming that all possible fields will be populated somewhere in the report and are required. The Lsplit and, possibly,  Rsplit functions are likely to be the main tools of the job. If any of the data fields may themselves contain commas some care will be required for those.

                   

                  Whilst neither of these approaches is really rookie activity normally they are not especially complex yet have the benefit of providing very useful experience that can be applied readily to similar situations.

                   

                  Let us know how much (if any!) sense this makes to you and we can take it from there.

                   

                   

                  HTH.

                   

                   

                  Grant