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:
ID ROUTE NUMBER OF RIDERS
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?