Access 2007 Brainfade : Can not think of method to filter certain results

[Jack Russel]

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 ?

[Micron]

What's best depends on how you have your data set up. In absence of any info on that and based on what you've shown, a query that returns TOP 2 from your query ought to work though.

[Jack Russel]

I can not use TOP 2 because the second combination is to be discarded. I have a table with Shooter info, a table with registration info, a table with result data and a query that calculates results.

The output from this query works fine for single entrants, I just can not see how I will filter out the "phantom" results from people who enter twice

[June7]

Why does query result in 4 combinations? Post the SQL.

Shooters won't have more than two for each category? Only two categories? Max() and Min() on the raw data don't get what you want?

MaxPistol: Max(IIf(Category="Pistol", Points, Null))

[Micron]

Well, I though this was your data

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

That's the excuse I'm sticking with!

[Jack Russel]

Here is the SQL :

SELECT Tbl_Shooters.Prénom, Pistolet.Tireur, [Nom] & " " & [Prénom] AS FullName, Pistolet.CommandeID AS PistInscr, PistoletResult.Results AS PisRes, Pistolet.Article AS PistCal, PistoletResult.Abbr AS PCalibre, Rifle.CommandeID AS RifInscr, Rifle.Article AS RifCal, RifleResult.Abbr AS RCalibre, RifleResult.Results AS RifRes, [RifRes]+[PisRes] AS ResultatCombi
FROM (Tbl_Shooters INNER JOIN (Tbl_Commandes AS Rifle INNER JOIN Qry_ResultatsIndividuels AS RifleResult ON Rifle.CommandeID = RifleResult.CommandeID) ON Tbl_Shooters.TireurID = Rifle.Tireur) INNER JOIN (Tbl_Commandes AS Pistolet INNER JOIN Qry_ResultatsIndividuels AS PistoletResult ON Pistolet.CommandeID = PistoletResult.CommandeID) ON Tbl_Shooters.TireurID = Pistolet.Tireur
WHERE (((PistoletResult.Results)>0) AND ((Pistolet.Article)=1 Or (Pistolet.Article)=2) AND ((Rifle.Article)=3 Or (Rifle.Article)=4) AND ((RifleResult.Results)>0));

[Jack Russel]

Here is a screen shot of the problem :



In this instance, the second line is the "best" combination and the fourth line is the "worst". the other two need to be filtered out as they are not real.

[Jack Russel]

Well, I though this was your data

That's the excuse I'm sticking with!

It is a good excuse ! I guess I have not been very clear Those results are from a single competitor, I have about 80 competitors.

[June7]

That's what I thought until I saw the second set of data.

Did you consider suggestion in post 4? Something like:

SELECT Results.Shooter, Max(IIf([Category]="Pistol",[Points],Null)) AS MaxPistol, Max(IIf([Category]="Rifle",[Points],Null)) AS MaxRifle, Min(IIf([Category]="Pistol",[Points],Null)) AS MinPistol, Min(IIf([Category]="Rifle",[Points],Null)) AS MinRifle
FROM Results
GROUP BY Results.Shooter;

[Micron]

Looks like a good solution. But did you mean Min as Min?
Max(IIf([Category]="Rifle",[Points],Null)) AS MinRifle

[June7]

Ooops, copy/paste strikes again. That second Max function for Rifle should be Min. I edited post.

[Jack Russel]

Hi Guys,

Thanks very much for the help, sorry I did not get back to you sooner, I dropped the project for a while and have only just got back to it. You suggestions worked perfectly ! Thanks again.