1 Reply Latest reply on Jun 20, 2011 11:58 AM by philmodjunk

    Making a  (simple ??)  calculation

    EssexBiker

      Title

      Making a  (simple ??)  calculation

      Post

      I have orders for tickets, I have only a finite number of seats available(capacity)on each bus (route) (there are many routes), 

      All the people are allocated a particular route according to the needs in a related table called Student

      All I am trying to do is in my routes table to show how many seats are occupied and how many seats are spare on the date i look at the report. This is going to be determined on how many valid (in date) tickets are in circulation for  a particular route and how many seats that bus has)

      Each individual route has it's own set capacity. With your help I have already worked a flag for whether a pass is valid on that day  (see the calculation below)

      SO if Route 510 has a capacity of 75 seats and in my orders table  the field Students::RouteID has say, 55 that are  "Valid"  my calculation should show 20 spare.

      Like wise on that day route 621 with a capacity of 50 seats and 30 valid passes would have 20 seats spare.

      I've tried every which way to create something that works properly, sadly failed on each occasionCry I've been fiddling around for days now trying to be brave, to no avail..

      Could some superhero help a little Dunce!

      Route Field Name               Field Type                 Formula / Entry Option

      OrderID                               Idexed

      StudentID                            Indexed

      Valid From                            Date

      Expires                                Date

      cToday                                 Calculation                If ( Get (CurrentDate )  ≥ ValidFrom and    Get (CurrentDate ) ≤ Expires ;                                                                           "Valid" ; "Invalid" )

      Count of Orders                    Summary = Count of StudentID

      Today Count                          Summary = Count of Today

      Table “Route”

      Route Field Name               Field Type     Formula / Entry Option

       

      RouteID                               Text

      RouteCapacity                      Number

      Spare Seats                        Calculation (Number)

      Table “Student”

      Route Field Name               Field Type     Formula / Entry Option

       

      StudentID                         Text            indexed

      RouteID                           Text

      (&& a lot more address fields etc!!)

        • 1. Re: Making a  (simple ??)  calculation
          philmodjunk

          Your Today Count and count of Orders fields will always return the same counts. You need a different field to count or sum for this to work.

          Try this field: If ( Get (CurrentDate )  ≥ ValidFrom and    Get (CurrentDate ) ≤ Expires ; "Valid" ; "" )

          And define Today Count as the count of it. (Count counts the records where the specified field is not empty.)

          If you pull up just the Orders records for one route at a time, this calculation should return the number of available seats:

          Route::RouteCapacity - Today Count

          If you pull up orders for multiple routs at one time and sort the records by Route, you'd need to use the get summary function.

          Route::RouteCapacity - GetSummary ( Today Count ; Student::RouteID )

          This asumes that you sort your Orders records by Student::RouteID to group them by Route.