3 Replies Latest reply on Feb 22, 2012 3:11 PM by philmodjunk

    Calculation Question



      Calculation Question


       I have a file that contains registration informaton for a cycling event.  When registering, the person chooses a distance to ride.  This is a character field because we might have a route called "100 mile express", or it may contain a number like 80.  The ride distances come from another table that has a record for each distance.  The registration table has an event ID and is joined to the routes table by event ID.  In the routes table, I want to calculate how many riders from registration are riding each distance. 

      So in my routes table, the data looks like:


      1    30         CALC

      1    45         CALC

      1    75         CALC

      1    100       CALC

      So the route field in every registration record has one of these selected.  I'd like to calculate the number of riders riding each distance in the NUMBER OF RIDERS field.  Is this possible in a calculated field?

        • 1. Re: Calculation Question

          This report can actually be produced from a layout based on the registration table.

          Create a new layout based on registration.

          Enter layout mode

          Double click the body part label to bring up part setup. Change the body into a sub summary part "when sorted by Route".

          Put a summary field in the sub summary part to display the number of riders who selected that route. Define this field as the "count of" another field in your table that is never empty, such as the route field. You can add other fields to this sub summary part to identify the route and the event if you wish.
          Return to browse mode and be sure to sort your records by Route--the same "when sorted by" field specified for the sub summary part.

          Note that by adding another sub summary part and grand summary parts, you can then enhance this report to list data from more than one event and also supply a event and grand totals counting the number of riders by using the same summary field in other parts of the same layout.

          Note that if your table contains registration data for more than one event, you'll need to perform a find for the desired records before sorting them to see your totals for each route if you only want to see data from a specific event.

          • 2. Re: Calculation Question

             Thanks, but I'm not after a report.  Since each event could have different routes, a table to keep the route information for the particular event is needed.  The routes table is displayed in a portal on the layout where the event is created.  So each portal row has a distance.  I'd like to display the number or fiders riding that distance next to it.

            • 3. Re: Calculation Question

              To do this from a portal will require a different approach.

              Set up this relationship between your routes table and your registration table:

              Routes::EventID = Registration::EventID AND
              Routes::Route = Registration::Route

              Since you may already have an existing relationship between routes and registration, you may need to use the duplicate button in mange | Database | relationships (two green plus signs) to make a copy of the Registration table in order to set up the above relationship.

              You can then either add the summary field I described earlier, from registration (select it from the exact same occurrence of Registration that you use for the above relationship) or you can define a calculation field in Routes defined as: Count ( Registration::EventID )