AnsweredAssumed Answered

Making a  (simple ??)  calculation

Question asked by EssexBiker on Jun 18, 2011
Latest reply on Nov 21, 2018 by jackrodgersjr


Making a  (simple ??)  calculation


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!!)