Calculation In Query

[jonsimons]

Pleaaasee.. Need help with query..

Hi,

I have this query, (you can see a picture off it in the attached file)..

I want to work out the position for the final by using the positions for the first two heats.

This is how it works: Say if a number came first in heat 1, and first in heat 2 then that number would be first in the final. Say if a number came last in heat 1 and heat 2, then that number would be last in the final. Basically their positions in the heats determine their positions in the final..

I hope I explained that ok..

Does anyone know of a way that I can make the query calculate this? Is it even possible?

Thanks

 

[Mile-O]

Can't you just add the two fields together and sort the result in descending order?

 

[jonsimons]

If I add the two fields together then that will just add their numbers together rather than their positions..

 

[Mile-O]

Maybe I'm not seeing where the final position is, then, for each heat. Can you explain the fields - Position, Heat1 and Heat2?

 

[jonsimons]

Yeh sorry.. im quite poor at explaining things

There are three car races... Heat 1, Heat 2 and the Final.

The position column.. 1,2,3 etc is what position each driver came in that race..

Every driver has their own number..

From that query.. you will see that in heat 1, driver number 66 came 1st, driver number 88 came second. In heat 2, driver number 99 came 1st, driver number 88 came second...

Column 3 - 'Final' is for the starting position for the final. I need to work out each drivers starting position. Their starting position depends on their results from the first two heats.
For example: If a driver came first in both heats, then he start first in the final. If a driver came last in both heats, then he would start last in the final.

So basically all I need to do is work out the drivers starting positions for the final from their results in the first two heats.

I hope this makes things clearer..

 

[Rich]

It would be easier if you normalised the data first

 

[jonsimons]

umm.. into what categories?

 

[Mile-O]

Something like this:

tblRace
RaceID
Race

tblCompetitor
CompetitorID
Forename
Surname

tblCompetitorsToRaces
CompetitorID
RaceID
Position

 

[thouston]

You are going to have to formulate some rules for what to do in the event of a tie. For example, in your original illustration, no 66 was first in the first heat, third in the second, while no 99 was third in the first heat, first in the second. I would put these as equal. Would you use a random number to distinguish between them, or automatically assign one of the heats to have greater weight? Also, what about no 88 who was second both times? This could be thought of as equal again (same average), or is a first place worth more? (You could alternatively argue that consistency is a virtue to be rewarded instead).

Once you work out what to do in these difficult cases, it may be easier to work out the best approach to coding it. Sorry if this makes it sound harder...

 

[jonsimons]

yeh..

I think the best way to work out the grid positions for the final is to give a driver so many points for finishing in a certain position...

For example... 10 points for 1st, 9 points for a 2nd, 8 points for a 3rd, 7 points for 4th etc.. Then to work out the grid position for the final, I will add up the drivers points for the 1st and 2nd heat, then the driver with the most points will start 1st etc..
If there are two drivers with the same number of points, then it will choose at random which driver will start where..

Do you know of an easy way for me to give a driver so many points for each position in each heat? Then to add the points from both heats up, and arrange the drivers in starting order?

thanks again for your help!

 

[The_Doc_Man]

You need to understand the concept of "ordinals" to figure this out.

Suppose that you have drivers who come in 1st, 2nd, 3rd, etc. Store these positions in the results table. In a query, compute the number of persons who beat each driver. This is just DCount("[position]","race table", "[position]<{this-driver's-position}" )

Do the same query for the second heat. (You might have to duplicate the first query to select the second heat.)

Now build a final table with every driver in it. Leave their position blank. Write an update query that does ANOTHER Dcount, this time counting the number of people with a lower average from the two heats. Add 1 to this count so if you have a clear winner, that person gets position 1.

Now, you have a couple of cases you will have to decide. Call 'em tie-breakers.

Suppose that in your first heat, the finishing order was 66, 88, 99, but in the second heat it was 99, 88, 66. You would find that all three of these have the same final average. (Car 66 gets a 0 and a 2; car 88 gets two 1's; car 99 gets a 2 and a 0; all of them add up to 2, average is 1, so you have three candidates for position 2 and NO clear candidates for position 1). So perhaps you would have a secondary query that looks at qualifying times as well as positions. You might need more than that as a tie-breaker. Perhaps weight of the vehicle, perhaps phase of the moon - who knows?

The idea is that rank-ordering (determining an ordinal position) is a well-known algorithm. You might wish to look up some terms on the internet such as rank-ordering, ordinal positioning, or general statistics of identifiable combinations. Or, for that matter, look up the scoring rules for Olympic figure skating. They use this rank-ordering technique to a fare-thee-well.

 

[jonsimons]

thanks for that solution..

i think ive also found another way round my problem.. i could just link the table to an excel sheet, i would carry out all the calculations in excel and then bring the data back to access.. volla

but.. would this work?

 

[Jon K]

You can do it in Access with a series of two queries: a Union Query followed by a Totals Query.

You can just treat the position of the two heats as the "points", so the driver with the least sum of points in the two heats is the first to start in the Final.

I have attached a database. When you run Query2, you have:-

Driver	SumOfPoints
99	4
88	4
66	4
6	8

So, with your sample data, driver 6 should start in the 4th position in the Final, whereas the rest three drivers are in a tie and can arbitrarily be assigned any of the first three positions.

If the times taken in heats 1 and 2 are also recorded, you can modify the queries to include the total time taken by each driver and sort the drivers by SumOfPoints and SumOfTime to resolve the tie issue.

 

[jonsimons]

Thanks Jon K!!!!

That works great!


God the world isan't such a bad place