Is DMin and DMax right?

[Navyguy]

Hi everybody

I volunteered to do a favour and once again I am over my head…

What I am looking at doing is a competition database and I am having trouble in determining if/how to use the Dmax, Dmin.

Each competitor is given 5 scores. I need to omit the lowest and the highest scores and then add the three remaining scores. Next I would omit the highest score and add the remaining four scores.

There will always be 5 scores, but they may be duplicated and they are 2 decimal places, eg 7.06.

Should I be using the Dmin and Dmax or should I be looking in another direction?

Thanks as always

 

[bradcccs]

Depends on your design...


Are the five scores in the one record or in seperate records?

ie:
Result1, 7.06, 8.25, 4.37, 4.98, 7.39

OR

Result1, Try1, 7.06
Result1, Try2, 8.25
Result1, Try3, 4.37
etc
etc

 

[Navyguy]

bradcccs

I guess I should have specified...

The scores would be all in one record. The score would be associated with an event number and a competitor name.

 

[bradcccs]

DMin & Dmax are used to locate the relevant value from a range of records, not from a number of fields in one record.

Haven't really needed to achieve your required outcome.

Will have a think (that hurts though) about the issue and get back to you if something comes to mind.

As an aside, would a different db structure cause you further issues. ie: Could you store the data as per structure 2 that I mentioned in previous post?
This design would also cater for more than 5 scores. (I know you said that there will "always" be 5, but the word always has a strange habit of having the word except added to it at a later date.

Brad.

 

[Navyguy]

Not Sure...

I think that if I am following the principles of normalization correctly, they need to be in one record.

When the kids do their routine, they are judged by five judges at the same time, so therefore I would think that the scores are related to that single event and thus related to that specific kid/person.

I could be wrong...and that would not be the first time!!!

 

[Pauldohert]

Maybe you should calculate the adjusted scores whan the data is entered and just save them to your table also - adds data to your table but the calculation is then only done once when they are entered not everytime you want to view the record.

 

[bradcccs]

Paul: Not good practice to store values that can be calculated.


NavyGuy:

I have had a bash at method 2 that I mentioned.

Using this method, you can exploit DMin / DMax etc etc in order to present the results as you wish.

You will see by the attached sample what I mean (I hope). (Access 2000)

This structure may not be as "simplified" as storing the results in one record, but removes the problem with querying your data. It also caters for the change in number of results (<>5).

You will see that I have performed the calculations in the actual form. You could of course do this in a query / report etc.

You may need to use CrossTab queries to display your data in one line if required for reporting processes etc.

Forgive the "raw" presentation etc. 'tis built for functionality only.

Let me know what you think.

Cheers

Brad.

 

[Pauldohert]

You are right Brad - but maybe all the database really needs is the final score- (I am presuming this will be based on the calculation as in most sports) so most of the reports and info to come from the database will be based on the calculated fields) its a shame to calculate the score each time its needed, when it is already known when it was entered.

Its not the correct way to do it - but if it means less work and doesn't interfere with performance - I see no reason - not to do it this way.

I would be interested what others think on this matter - maybe I will mend my ways! ;-)

 

[bradcccs]

Not wanting to open a can of worms . . . but

its a shame to calculate the score each time its needed, when it is already known when it was entered.

How is the "calculated" field known when entered?

The input is of raw data.

??

 

[Pauldohert]

I mean all the information needed to calculate the score is available when the data is entered - If you do the calculation once the score is then known.

In a practical world rather than a perfect on - it may be easier to store that bit of knowledge and use that as the starting point for future calculations.

 

[bradcccs]

We may have to agree to disagree on this one paul.

In my basic understanding of db structure, your proposal violates data storage procedures.

Also: Going back to NavyGuy's original issue, is it very difficult to calculate the required "result" from a flat data record. (I would be interested in seeing how the desired calculations can be achieved at data entry of a flat record.)

Anywho. Here's hoping Navyguy has a solution.

Cheers

Brad.

 

[Pauldohert]

Cheers Brad - Its good to question the way I do things - I don't want to be doing things completely wrong.


From NavyGuys question to work out the score in vba is easy compare the lowest of two values with the next - the lowest of those two values with the next etc etc etc and you have your answer. (again I'm sure there are better ways but this will do)

Is not as simple ( for me anyway) to calculate this each time I run a query or something)

 

[Navyguy]

Maybe Arrays?

Hi there guys

I appreciate the discussion and certainly worth thinking about.

Brad I had a look at your sample, and you do get the required result but...

In my simple mind I see 5 records per kid, so lets say an average size competition is 300 kids and they each do 3 events (some more, some less)...I see 4500 records as a result.

True enough this is a "one time application" of the DB, but it does seem to "swim upstream" when only 900 records would do the same thing.

I am just going through some of my books here and I am thinking about arrays now, but have not read enough to determine if this is the right direction either.

I have to admit, I thought there would be a simplier way to do this...

Thanks for all your help so far, and your sample may end up being the final product!!!

 

[Navyguy]

Something hit the radar

I read what I could today and I think that arrays are not the way to go, however temporary tables did make a "blip" on the screen today.

If a temp table was made to "re-arrange" the data as Brad suggests, then the data could be maintained in a normalized format, brought into a temp table for the qry/rpt etc and then deleted after the qry/rpt was completed.

In this case, would this just take up to many resources and would it be better to maintain an un-normalized format? I have never worked with temp tables before so I am not even sure if what I am suggesting can be done.

Thoughts anyone…

 

[Jon K]

.... I think that arrays are not the way to go

Array is one way to go.

The attached database contains two generic functions (each uses an array) to find the minimum and the maximum values from a list of values. The functions are in Module1.

The query uses the functions to omit the highest and the lowest scores.

 

[Navyguy]

It's a Winner

Thanks Jon K

I did read about arrays and thought they were not the answer, but I guess I did not throughly understand how to apply them.

I had a look at the qry and the module, and I am not sure how they work together. Where in the SQL does it call the module? Would you be willing to explain the module so I can get a bit of an understanding of how it works?

Thanks again, this certainly solves the normalization issue I was wrestling with.

 

[Jon K]

Function [b]MaxValue[/b](ParamArray aFld())
   Dim i As Integer
   Dim Value

   Value = aFld(0)
   For i = 1 To UBound(aFld)
      If aFld(i) > Value Then
         Value = aFld(i)
      End If
   Next i
   MaxValue = Value

End Function

A function placed in a module is public. You can call it from queries, reports, forms, or VBA simply by using the function's name.

When the query calls the function with:-
Omit Highest Score: val(Score1 + Score2 + Score3 + Score4 + Score5 - MaxValue(Score1, Score2, Score3, Score4, Score5))

the function places the list of values passed to it in an array aFld():
aFld(0) holds value of Score1
aFld(1) holds value of Score2
aFld(2) holds value of Score3
aFld(3) holds value of Score4
aFld(4) holds value of Score5

The function then uses Value=aFld(0) to place the value of Score1 in the Value variable. Then in a For Loop, it compares Value with aFld(1) up to aFld(4) one at a time, using aFld(i).

UBound(aFld) can find the largest available subscript in the array, so
For i=1 to UBound(aFld) is actually equivalent to For i=1 to 4. Using UBound(aFld) instead of 4 makes the function generic.

Each time if aFld(i) > Value, then that value is placed in Value before comparing it with the next aFld(i).

So in the end, Value should hold the maximum value from the list and the function just returns that value to the query.


The query will add up the 5 scores and minus the maximum value returned to arrive at the field [Omit Highest Score].

The val() function in the query can help to suppress (successfully most of the time) long decimal places due to type conversion during calculation e.g. without val(), the query in the example will return

Competitor	Score1	Score2	Score3	Score4	Score5	   Middle 3 Scores	Omit Highest Score
001		  7.06	  8.25	  4.37	  4.98	  7.39	  19.4300003051758	  23.7999992370605
002		     1	     2	     3	     4	     5		         9	                10
003		     5	     4	     3	     2	     1		         9	                10
004		    55	    33	    11	    22	    44		        99	               110
005		     2	     2	   1.9	     5	   4.5		       8.5	  10.3999996185303

The structure of the MinValue() function is the same as that of the MaxValue() function except for the < sign.

Hope this helps.

 

[Navyguy]

Jon

I would like to thank you for taking the time to explain that for me. I will take the time and try to absorb what you have written and try to make use of arrays in my practice DBs to become more familar with them.

I can see many possible uses for this type of function.