6 Replies Latest reply on Jan 28, 2017 3:01 PM by philmodjunk

    calculation help needed

    shae1725

      I have a single table containing addresses for marketing

      the table contains multiple contacts at the same address.

       

      My goal is to work out how many physical Front Doors I'm going to have to knock.

      the table contains a postcode/zip field and a house number field.

       

      Any help with a calculation would be appreciated

        • 1. Re: calculation help needed
          philmodjunk

          A related table of addresses that you link to contacts would work better. Not only does this allow you to avoid entering the same address more than once, it eliminates issues where minor differences in how an address was entered might keep the same address from being identified as the same address.

           

          Someone might have an address of 2310 FileMaker Street and another contact might have an address of 2310 Filemaker St. and then you have two different street names for what is actually the same address.

           

          If you have perfect addresses (that thus do not have this issue), you can use ExecuteSQL with the Distinct keyword to get a count of unique addresses in your table.

          • 2. Re: calculation help needed
            shae1725

            Hi Phil

             

            I'm working with a previously compiled dataset

             

            No one house number is repeated on a single postcode/zip, so its pretty close to perfect addresses.

             

            as far as ExecuteSql is concerned i have no experience im afraid

            • 3. Re: calculation help needed
              philmodjunk

              That seems very unusual that you wouldn't have two house numbers with the same zip. Your contacts must be widely separated from each other.

               

              You have multiple options.

               

              1) There's a method that does this with a summary report and a method called "sum the reciprocal".

               

              2) You can define a calculation field that combines house number and zip, then define a 'use values from field' value lists that lists the contents of this calculation field. Value lists don't allow duplicates so:

              ValueCount ( ValueListItemst ( "" ; "YourValueListnameHere" ) )

               

              will count the addresses without counting duplicates.

               

              3) You could set up that related table of addresses and just count the number of records in that table. (You link such addresses by an address ID to the contacts table unless you need a many to many relationship.)

               

              The last option gives you your count and moves you towards a better database design.

              • 4. Re: calculation help needed
                shae1725

                I used option 2 although it does the job i can see problems as database expands.

                 

                Dont really understand option three to try it

                 

                Many Thanks

                • 5. Re: calculation help needed
                  JackRodges

                  The US zip codes have a four or five digit add on xxxxx-xxxx that identifies unique mailboxes within a zip code.

                   

                  The PO offers a standardization of addresses to remove the human volatility of data entry.

                   

                  Using these would be a good step and the 9 digit zip code would be a key as you could create a relationship on it to show all shared addresses.

                  • 6. Re: calculation help needed
                    philmodjunk

                    Good point about "zip plus four" as it is called, but many individuals do not know their "plus four". I couldn't tell you mine without looking it up. So it may not be practical option in all cases.

                     

                    Option 3 uses a standard principle of relational database design: do not enter the same data into more than one location. Instead, enter it once and link to it wherever needed.

                     

                    You would use this basic data model:

                     

                    contacts>----Addresses

                     

                    Contacts::_fkAddressID = Addresses::__pkAddressID

                     

                    __pkAddressID would be a field that auto-enters a serial number or get(UUID).

                     

                    I would not use a zipplus four code in this relationship but I might use it in a secondary relationship to check for duplicate addresses.