4 Replies Latest reply on Jul 22, 2010 8:14 AM by Mary78

    Counting related records



      Counting related records



       I have 2 tables which are related via a Client ID field. They are called Personal Information and Support Visits. Each client has an individaul ID numer (client ID). Clients may have a number of Support Visit records.

      When doing a find in Support Visits, e.g., location of supprt visit, I would like to be able to find the number of Clients this might involve.

      Is there a way I can have a calculation field in the Supoprt Visits table which will automatically count the number of clients within a particular found set.

      Many thanks



        • 1. Re: Counting related records

          Howdy Mary78,

          Assumption: There is a field in your Personal Information table that contains data identifying it as a record of interest for a given support visit...Your "Location" field perhaps?

          Create another Table Occurrence of your PersonalInfo table {I'll call it PersonalInfo2}, link it to your SupportVisit table by the "Location" field.

          Now set up a calc field in your SupportVisit table which is defined as Count(PersonalInfo2::Location).

          If you haven't used multiple Table Occurences yet, read up on them.  Not Tables ... Table Occurences.

          Is this what you're after?  If not, could you describe in more detail what you are trying to do, and what fields you have to accomplish it?

          • 2. Re: Counting related records

            Hi Ninja,

            Many thanks for this. I have tried it but it doesn't seem to work (yet).

            I have a number of different fields in the Supportvisit table, e.g., support worker, location, services referred to etc. For reports, I need to show how many visits took place at a particular location and how many clients were seen (some may have numerous visits).

            Similarly, I need to show how many visits a particular support worker undertook and with how many clients etc. I have a button on the SUpportvisit table already which gets all related records in the Personalinfo table. This brings me to the Personal info table and shows the records any find in support visits relates to.

            THis means that I have to keep switching between the 2 tables any time I want to do a number of searches. It would be easier for me if there was a field in the supportvisit table which would show the number of clients for each find.

            I have already put together subsummary reports for each table, but I am still faced with the same problem of not getting the number of clients.

            Any help would be greatly appreciated.


            • 3. Re: Counting related records

              If I'm reading your posts correctly, the challenge to correctly counting clients is that you may have any number of "visit" records for the same client in your report's found set of records.

              Here's a technique I learned from comment that will count "unique entries" and should do what you need for your client count:

              1. Define a summary field, sVisits, as the count of your client ID field in Support Visits.
              2. Define a calculation field, cFraction as 1 / GetSummary ( sVisits ; ClientID )
              3. Define a second summary field, sClientCount a the Total of cFraction.


              Place sClientCount in a header, footer or grand summary part, sort the records by ClientID and you'll get the total count of clients in your Support Visits based report.

              • 4. Re: Counting related records

                Thanks a million for this. Great to have this working after weeks of faffing about.