Expression in a Query

[627dmp]

I am trying to create a query that does the following:

I have a database for keeping score for trapshooting, the shooters shoot 50
times and out of those 50 shots they divide their score by 2, so if a shooter
hit all 50 then we would say their handicap yardage is 25, or if they hit 40,
their yardage would be 20. Which I have no problem doing in my query, but
then what I need to do is put them into squads based on their handicap
yardage, so I want to group everyone who ended up with yardage of 25 & 24
together, 23 & 22 together, 21 & 20 together and 19 & 18 together. I then
need to assign them a squad number with 5 shooters in each squad. Then I
need to assign a position of 1 thru 5 to each shooter in each squad. So if
we have 50 shooters, we should have 10 squads with positions 1 thru 5.

 

[KARL DEWEY]

Try this --
SELECT [627dmp].Shooter, [627dmp].Score, (((SELECT Count(*) FROM [627dmp]
AS [XX] WHERE [XX].[Score] +(1/[XX].Shooter) <= [627dmp].[Score]
+(1/[627dmp].Shooter))-1)\5)+1 AS Shooter_Group
FROM 627dmp;

 

[KARL DEWEY]

After running it later I needed to follow it with another query --
Shooter_Group_Final --
SELECT Shooter_Group.Shooter_Group, Shooter_Group.Score, Shooter_Group.Shooter
FROM Shooter_Group
ORDER BY Shooter_Group.Shooter_Group, Shooter_Group.Score,
Shooter_Group.Shooter;

--
Build a little, test a little.

Try this --
SELECT [627dmp].Shooter, [627dmp].Score, (((SELECT Count(*) FROM [627dmp]
AS [XX] WHERE [XX].[Score] +(1/[XX].Shooter) <= [627dmp].[Score]
+(1/[627dmp].Shooter))-1)\5)+1 AS Shooter_Group
FROM 627dmp;

--
Build a little, test a little.

I am trying to create a query that does the following:

I have a database for keeping score for trapshooting, the shooters shoot 50
times and out of those 50 shots they divide their score by 2, so if a shooter
hit all 50 then we would say their handicap yardage is 25, or if they hit 40,
their yardage would be 20. Which I have no problem doing in my query, but
then what I need to do is put them into squads based on their handicap
yardage, so I want to group everyone who ended up with yardage of 25 & 24
together, 23 & 22 together, 21 & 20 together and 19 & 18 together. I then
need to assign them a squad number with 5 shooters in each squad. Then I
need to assign a position of 1 thru 5 to each shooter in each squad. So if
we have 50 shooters, we should have 10 squads with positions 1 thru 5.