6 Replies Latest reply on Oct 18, 2011 11:39 AM by HelderSantos

    my goal is to make the total count of users per province

    HelderSantos

      Title

      my goal is to make the total count of users per province

      Post

      I have three tables, the first is "provinces" second "user" and third "cadasto" makes registration of users in the provinces.
      my goal is to make the total count of users per province.

      someone help, please?

        • 1. Re: my goal is to make the total count of users per province
          philmodjunk

          How have you defined the relationships between these three tables. Is it like this?

          Provinces----<cadasto>-----User

          Provinces::ProvincesID = cadasto::ProvincesID
          User::UserID = cadasto::UserID

          If so, a summary report can be created in a layout based on cadasto that can have one line for each Province with the count of users registered in that province listed with each Province name. This uses a "count of" summary field placed inside a sub summary part "when sorted by" Province. The body layout part is removed and the records must be sorted by the same field specified in part set up for the sub summary part.

          • 2. Re: my goal is to make the total count of users per province
            HelderSantos

            yes my relationships is like that.

            Unfortunatly, it doesn't work. :( 

            i worked before with some others databases, i could build up ("select ... group by..." is done). i trust in FMker. how can i share my file so u can take a loock

            • 3. Re: my goal is to make the total count of users per province
              philmodjunk

              It does indeed work. I use this method frequently.

              Start a new layout and specify Cadasto as the "show records from" table occurrence.

              Enter layout mode and double click the body layout part to open up part setup.

              Change the body layout part to a sub summary part "when sorted by" and specify the Name field in Provinces if you want an alphabetical list sorted by Province name.

              Put this same field in the sub summary part and put the summary field I described earlier next to it. Return to browse mode and sort your records by the same province name field. This gives you a count of all records in your current found set. you can perform finds to count the records for each province for sub sets of your total records. You will always need to sort your records after each such find.

              It's also possible to do your counting from a layout based on provinces, though this can be a less flexible approach if you don't want to count all the records in the cadasto table. In this case, you can place the same summary field from Cadasto in the body of the layout. No sorting is needed, but the relationship will match to all records in cadasto for a given province, not a specified sub set so this may or may not work for you without using a more sophisticated relationship or portal filter--neither of which are needed if you base the layout on the Cadasto table.

              On the other hand, a report based on Province can show a count of zero for a given province where a report based on Cadasto will not list a province with at zero count at all as there won't be any record in Cadasto for that province in those cases.

              • 4. Re: my goal is to make the total count of users per province
                HelderSantos

                i know you are good guys, i am trying to get it... i dont want to give up!

                let me take a quick rest is almost 15min to midi-night, i cant continue...

                • 5. Re: my goal is to make the total count of users per province
                  philmodjunk

                  Take a look at this demo file. It illustrates counting records in the join table from both a layout based on the Join table and also for a layout based on the provinces table.

                  http://www.4shared.com/file/ybqsOzQn/ProvincesUsersDemo.html

                  • 6. Re: my goal is to make the total count of users per province
                    HelderSantos

                    PhilModJunk  your demo is good. i got it.

                     Thank you.