Formula Help

[lionking15]

Hi

Please could someone help me with a formula.

My database consists of winners of a horse trainer
In my browse i have a distance column that shows a distance for each record. The distances are anywhere from 1000 meters to 3000 meters. I would like to display on my form the trainers best distance. In other words the distance that is mostly entered into the database.

I am also trying to display on the form, when i am on a certain horse the shortest and longest distance it has won, eg "1200m - 1800m"

Please could anyone try help

Thanks
Cliff

[Davidk]

Re: Formula Help

For shortest and longest distance you could use DBMin() and DBMax() to get the smallest and largest values in your table. The help file is really good for illustrating the syntax for these.

Your other question kinda sounds like 2 different things though. You want the "trainers best distance" but then you say you want the "distance that is mostly entered into the database" which are different.

So, for a trainer that has one win at 3000 meters and the same trainer has two wins at 2000 meters, which record would you want. The longest at 3000 or the mostly entered at 2000 x 2?

[lionking15]

Re: Formula Help

Hi David.

Thank you for your help.

I need the mostly entered, that is 2000 x 2, but it doesnt have to display the 2, just the 2000.

Thank You Again
Cliff

[lionking15]

Re: Formula Help

David in the dbmax syntax the "key value" is a constant, how do i make it a variable, otherwise it will give me the same answer whatever record that is showing in the form?

Thanks
Cliff

[Davidk]

Re: Formula Help

Key_Value can be anything - it doesn't have to be a literal.

Here's one I use...

MaxClmImageNbr = dbmax("tblClaimImages", "ClmNbr", ClaimNbr.value,"ImageNbr")

where ClaimNbr.Value is a field on a form. This code is run from a button on the same form.

The "mostly entered" you could do this way...

Create a new Operation - Summarize Records.
Try using the Genie. Group by Trainer, Group by Distance, and Count DistanceThis will give you a table Grouped by Trainer, Grouped by the Distance Amounts and give a count of each Trainer/Distance Group. Here's a picture of the Operation.

Once you've got the table, you can access it in code and Order the table in descending order to get the highest count of each Distance Group.

There's a lot here, so if you want more detail just shout.