Greetings one and all !
I have a database in Access 2007 which has been built to score a shooting match. The database calculates the results in different categories and calibres etc and produces several corresponding reports. Everything is working fine except for one set of results which I can not get my head around.
I have a query which combines the best pistol score with the best rifle score which is easy enough, but some competitors enter twice with different calibres and the best score of each entry needs to be combined.
The query puts the scores together in four combinations :
Pistol1 + Rifle1
Pistol1 + Rifle2
Pistol2 + Rifle1
Pistol2 + Rifle2
The highest scoring set needs to be retained, and the two "false results" binned and the remaining result retained. So for example :
Pistol 1 = 1000 Points
Pistol 2 = 800
Rifle 1 = 600
Rifle 2 = 500
I currently get 4 results :
a) 1000 + 600 = 1,600 points
b) 1000 + 500 = 1,500
c) 800 + 600 = 1,400
d) 800 + 500 = 1,300
The best result is obviously a. Then b and c need to be eliminated leaving d as the second result.
There are roughly 100 records in the query, some with double entries some with single.
I can not think of a way to efficiently do this.
Any thoughts ?