School busing database for busing fee based on address distance from school (one database)

Fields:

student name

school

grade

whole address (calculated field based on #, street name)

distance

fee_required "Yes" or "No" calculated field based on distance from school and grade

Fee = $225 for each student who lives less than 2 mi from the school and is in grade K-6

fee_Total - HOW DO I WRITE A CALCULATION TO TOTAL THE FEES FOR EACH ADDRESS? One address may have 3 students going to 3 schools; 2 require a fee and 1 does not. How do I get the total for that address?

THANKS - very new to FileMaker and using version 6.0

DISTRICTClone.zip

Will your users tell the database whether a student is less than 2 miles from a school, or were you looking for a way to calculate this from the address? (This can be calculated from latitude/longitude, but it's not a simple matter.)

We already ran it through a software program to calculate the distance, so I have that field already filled out with the mileage. That mileage then determines whether the field_required field is yes or no. What I wanted to do was add up all the "yes's" for a specific address and get to a total.

thanks

Do you have a Household or Family file related to the Student, or should we assume that duplicate addresses are always part of the same family?

let's assume the duplicate addresses are part of the same family for now

Try changing your Fee_Total field to a Summary (Total of Fee_Amount) field.

With this in your sub-summary by wholeAddress part, you should be able to see the total Fees for each address.

I'd recommend you consider a relational design, as this will simplify data entry and give you more flexibility later.