3 Replies Latest reply on Jun 10, 2013 3:12 PM by philmodjunk

    Count of addresses

    KurtSnow

      Title

      Count of addresses

      Post

           I need to count the unique addresses in a found set in my radio company database. A company may have more than one radio station at the address.

           I created a field called "GetAddress" = Count ( MediaCompany::physicalAddress )

           Then a summary field = Total of GetAddress

           I created a report that has a sub-summary of address. The unqiue addresses show up correctly, but instead of 8 in the summary filed, it counts 10 since there are two companies with two radio stations at the same address.

           What am I doing wrong?

        • 1. Re: Count of addresses
          philmodjunk

               Count simply counts the number of related records where the address field is not empty. It makes no effort to count unique instances of the data in that field.

               The most straight forward fix is to not have records in mediaCompany with duplicate addresses. The addresses would be stored in a related table and when two companies have the same physical address, they'd both link to the same record in the table of addresses.

          • 2. Re: Count of addresses
            KurtSnow

                 Do you know of a tutorial, or have a link to a web page, that can help me understand creating a table within the same file? In my previoius experience with databases (Lotus Approach) all of the databases one created were external and linked as exteranl relationships. When and why would I create a table within the same file? I am not sure if the address table you are recommending should be an exteran database or a table within hte mediaCompany file.

                 Note: For the mediaCompany database I have over 2,000 records that I imported from Approach.

            • 3. Re: Count of addresses
              philmodjunk

                   In FileMaker, tables may be defined in the same file or separate files. Once linked in relationships they function much the same either way. Creating multiple tables in the same file is simpler and generally easier to work with than separate files as it avoids a number of complications that have to be dealt with when you use separate files for each table.

                   To add a table to your database, you go to manate | Database from the file menu, click the Tables tab/button enter a name for the table and click Create. Then you use the Fields section to define fields for it and the relationships section to link that table to other tables in relationships. When you define a new table, FileMaker creates a Tutorial: What are Table Occurrences? and a layout of exactly the same name. You can then create as many additional table occurrences and layouts based on that table as you need.