4 Replies Latest reply: May 15, 2014 10:07 AM by Grant Perkins RSS

    How to filter for adjacent values of a field being the same?

    MonUserCJ _

      Hey all,

       

         I am interested in creating a filter that will show me all rows where the value of a given field is the same as the record below it. So, for example, if I were extracting names from a report, and two consecutive rows had the name, "John Doe", I would want it to return the first of those two rows (or both of them, I guess). However, I would not want rows to be mentioned simply because a field value occurs twice in the data--only when the identical field values are "next to each other".

       

         Does anyone know how I could go about doing this? I was thinking that maybe I could use a formula-based filter if I knew how to reference the next row or the previous row, etc. Does anyone know of a way to do this? I would appreciate any advice that anyone can give.

       

         Thanks.

        • How to filter for adjacent values of a field being the same?
          Olly Bond

          Hello CJ,

           

          Sorry to be the bearer of bad news, but it can't be done in one pass. It can be done in two, however...

           

          Step one, obtain your table, add a calculated field called Record with the expression Recno()+1, export to MDB.

           

          Step two, open your original table, and make a calculated field called Record, expression, Recno(), then make an external lookup to the MDB, match on Record, bring in the fields you need to compare.

           

          HTH,

           

          Olly

            • How to filter for adjacent values of a field being the same?
              MonUserCJ _

              Hello CJ,

               

              Sorry to be the bearer of bad news, but it can't be done in one pass. It can be done in two, however...

               

              Step one, obtain your table, add a calculated field called Record with the expression Recno()+1, export to MDB.

               

              Step two, open your original table, and make a calculated field called Record, expression, Recno(), then make an external lookup to the MDB, match on Record, bring in the fields you need to compare.

               

              HTH,

               

              Olly[/QUOTE]

               

              Hi,

               

              Thanks, Olly. That sounds like an interesting method. However, I am getting an error when I try to make the external lookup: "An external lookup data source is either missing or invalid..."

               

              Can anyone help me figure out where I went wrong? I added the calculated field, exported the table to an MDB file (using File-->Export). Then, without closing Monarch, I edited the calculated Record field. Then I go to Data, External Lookups, and select to Link via the Record fields. Then, I select a couple of other fields to bring along. When I go to the next step, an error is produced.

               

              Is it some issue with identical field names between the two tables or something? I'm not sure what to make of it. I'd appreciate any advice anyone has.

                • How to filter for adjacent values of a field being the same?
                  MonUserCJ _

                  Hey all,

                   

                     Pardon me for inadvertently bumping the thread, but I like to try to followup on my own threads if I find a solution in case someone has a similar problem in the future.

                   

                     I was having the same problem today. I think I fixed it by ensuring that the field that I was using to do the external lookup on was formatted as Text (rather than General).

                    • How to filter for adjacent values of a field being the same?
                      Grant Perkins

                      In general (no pun intended) I always assume that ther might be subtle differences between the field definitions of databases (a Monarch table being a database) and that it is best to assume the need to be explicit for the definition of linked fields, whether for look up purposes or when exporting to existing tables.

                       

                      'General' format is a bit of a loose presentation for that purpose, though convenient for display and presentation purposes.

                       

                      Lookup links really need to be carefully selected and controlled to ensure that in the worst case examples valid matches still occur. Even relatively simple things like left or right justification of character strings that are shorter than the maximum field length can cause missed matches even though the field format types are correctly matched.

                       

                      (NB. I expect most people have already experienced this sort of problem in one form or another but some will not have seen it, so this information is offered in the spirit of seeking to help those who find this thread at a later date just as MonUserCJ has proposed.)

                       

                      HTH.

                       

                       

                      Grant