5 Replies Latest reply on Jun 6, 2012 7:57 AM by mennomulders

    Counting related records




      I have a problem with counting related records in the following database, which is used for inspecting fire hydrants, valves monitors e.d.


      The database consists of a a table with companies and a table of items. These tables are related by a company number.


      The items have an ITEM ID, and a type letter ( A for hydrant, B for valve, C for monitor and so on until T)


      What I would like to see in the Company record is the total amount of items (so I used the count function, is working great), but also the amount of items sorted by type. I don't even know if is is possible but it sounds so simple, however, I can't figure it out.


      Any help?


      Thanks a lot,

        • 1. Re: Counting related records

          You can do this a couple of different ways. If you want to have a live count (always available, always accurate), you can set up separate relationships between calculated fields in the master table that always evaluate to the type letter (one for "A", "B", "C", etc.) and then use a calculation to have the totals evaluate.


          Yuck. That's a lot of fields.


          Another option would be to have a global field that relates to the type in the target table. Allow the user to select the Type from the list, then have a calculation field that does a Count at that time.


          Yet a third option would be to run a script that goes out, finds all the records of each type, and stores the values in a common single-record table, which you can then access from elsewhere.


          Depends on exactly what you need.





          • 2. Re: Counting related records
            Stephen Huston

            And another option, if you only need this as read info on the screen at times, would be to Go To Related Records on a layout for the related records where you sort them by the type, showing a sub-summary part with a Count of records (found set) in the sub-summary to tell you how many of each at that moment.

            • 3. Re: Counting related records

              You could use a table of Types with a global gCompanyID field, and let each record count the selected company's items by type.

              Use a script trigger to populate the global field with the currently viewed CompanyID.

              • 4. Re: Counting related records

                One more option would be to use the ExecuteSQL and store the result in a field. Something like: ExecuteSQL( "SELECT COUNT(Item_Id) FROM Items WHERE Type='A' AND Company_Id='1' ; "" ; "").

                • 5. Re: Counting related records

                  Thanks a lot every one, Michaels solution will work the best for me