Maximum Summary field not working
I have Table A with all of the delay records. I have Table B that counts them by airports using a summary field in a table relationship. I have Table C that I want to grab the top five from Table B using maximum summary fields in a table relationship with Table B.
Here is Table B. When I add a summary field in the relationship with Table C, I put in maximum and it returns nothing. Below is what I'm using for the summary field. Both of the fields are numeric.
Does anyone have any idea why this might be happening? I followed a Kirk Trachy YouTube video explaining how to do this and followed the direction exact to what he showed in the video.
I'm not sure which video you are referring to but Kirk Trachy (now retired from Quickbase) is my absolute hero.
Yes this is the way I would approach it. It involves a lot of repetitive summary fields and look up some looping but I think it'll work. The end result will be a single record in table see with the airport codes of the top five airports that have delays.
If you have a single record in Table C, it will be Record ID# 1.
Make a field on your Airports table, which is a formula field of
1
And then use that as the reference field on the right side of a relationship to connect every airport record with the table C airport top five table.
Make a summary maximum of # delays called [Max # Delays 1].
Lookup down to Airports.
Make a summary Maximum of the [Record ID#] subject to the filer that the [Max # Delays 1] equal the [# of delays]. Call it [Record ID# of Airport with Delays 1].
Look that up down to Airports.
Make a Combined text summary field of the Airport code subject to the filter that the [Record ID#]= [Record ID# of Airport with Delays 1].
You don't like the bubble like appearance of that airport code you can make a new field which just is a ToText(...) Function on that combine text summary field so that it looks like a normal text field.
Then you start all over again except when you make a maximum of the number of delays, you introduce a filter that the number of delays is greater than or equal to the [Max # Delays 1]. We have to say greater than or equal to because there could be two or airports that are tied with the same number of delays.
Just repeat all the steps above and you'll end up with the airport code for the airport with the 2nd highest # of delays.
Then repeat the process three more times.
