# Special Sort (1 Viewer)

#### MajP

##### You've got your good things, and you've got mine.
I may not fully understand the data because you said there was only 2 athletes per event. But in the 100 here are the counts
qry100Count qry100Count

SchoolAssociationCountOfAthleteID
Nongstoin District Sports Association
9​
Tura District Athletic Association
8​
Shillong Sports Association
8​
West Jaintia Hills District Sports Association
7​
North Garo Hills District Association
7​
South Garo Hills District Athletic Association
4​
Ri Bhoi District Sports Association
4​
East Garo Hills District Sports Association
4​
Mawkyrwat District Sports Association
3​
South West Garo Hills District Association
2​
East Jaintia Hills District Athletic Association
2​
So there are 8 heats calculated base on 58 athletes (58/8), but should there be another check and make sure there are 9 heats so that Nongstoin does not have 2 runners in the same heat.

#### MajP

##### You've got your good things, and you've got mine.
After rereading I think your algorithm tries maximize the max amount of athletes per heat, not minimize the difference between the heats. This is a little more complicated with the rule of no two athletes from the same school. This goes to Cronk's questjion in 18. I think this may work. I seemed to get the correct numbers.
Code:
``````Public Sub AssignHeats2(TheEvent As String)
'Maximize the number of heats with 8 not to have one heat less than 3

Const NumberOfLanes = 8
Const MinParticipants = 3

Dim rs As DAO.Recordset
Dim AthleteCount As Integer
Dim Heats As Integer
Dim HeatCounter As Integer
Dim MaxAthletesPerHeat As Integer
Dim athleteCounter As Integer
Dim minAthletesPerHeat As Integer
Dim StopLast As Integer
Dim assignableHeats As Integer
Dim fullHeats As Integer

Set rs = CurrentDb.OpenRecordset("Select * from AthleteEvent where event = '" & TheEvent & "' ORDER BY SchoolAssociation, AthleteID")
If Not rs.EOF Then
rs.MoveLast
rs.MoveFirst
AthleteCount = rs.RecordCount
Heats = RoundUp(AthleteCount / NumberOfLanes)
MaxAthletesPerHeat = RoundUp(AthleteCount / Heats)
If AthleteCount Mod NumberOfLanes = 0 Then
fullHeats = Heats
Else
fullHeats = Heats - 1
End If
minAthletesPerHeat = AthleteCount - (MaxAthletesPerHeat * fullHeats)
If minAthletesPerHeat <> 0 Then
StopLast = Heats * minAthletesPerHeat
End If
End If

'After you fill the last heat to its max only fill the remainder.
assignableHeats = Heats
Do While Not rs.EOF
athleteCounter = athleteCounter + 1
HeatCounter = HeatCounter + 1
rs.Edit
rs!Heat = HeatCounter
rs.Update
rs.MoveNext
If HeatCounter = assignableHeats Then HeatCounter = 0
If athleteCounter = StopLast Then assignableHeats = assignableHeats - 1
Loop

End Sub``````

So you determine how many can you fill all the way to 8 and what is left over. For example in your example of 44. You will have 5 Full Heats and 1 heat of 4.
To do this start reading the names sorted by School and Athlete. Start filling all the heats (1 to 6) until the last heat has 4 people in it. That will happen after you get to 24th athlete. At that time all the heats have 4 in them. At that point continue to fill only 1 to 5. Kind of convoluted but seems to be close or correct.

#### Cronk

##### Registered User.
The getting of the OP's business rules has been like drawing teeth. In formulation heats, I expect there will also be restrictions on gender, category (eg Under 16) and event type (eg 100m, 200m)

#### amirkharkongor

##### Member
Hi Friends,

Thanks for your support but for now I really need just the sorting logic. if my data is in the following order

BIbNoAthleteNameSchoolAssociation
046ANIMA ARENGHEast Garo Hills District Sports Association
248Wanda LyngkhoiNongstoin District Sports Association
246Tiewlari RynshiangNongstoin District Sports Association
190SILTE G. MOMINNorth Garo Hills District Association
073SHIDALIN RONGPIRi Bhoi District Sports Association
025Feliscita RynjahShillong Sports Association
162MIRIME D. SANGMATura District Athletic Association
161RINGRINGCHI K. SANGMATura District Athletic Association
320BEAUTIFY SHYLLAWest Jaintia Hills District Sports Association
220IAISHAH DKHARWest Jaintia Hills District Sports Association

I just want to sort the SchoolAssociation in the following order

BIbNoAthleteNameSchoolAssociation
046ANIMA ARENGHEast Garo Hills District Sports Association
248Wanda LyngkhoiNongstoin District Sports Association
190SILTE G. MOMINNorth Garo Hills District Association
073SHIDALIN RONGPIRi Bhoi District Sports Association
025Feliscita RynjahShillong Sports Association
162MIRIME D. SANGMATura District Athletic Association
320BEAUTIFY SHYLLAWest Jaintia Hills District Sports Association
246Tiewlari RynshiangNongstoin District Sports Association
161RINGRINGCHI K. SANGMATura District Athletic Association
220IAISHAH DKHARWest Jaintia Hills District Sports Association

I tried the solution provided in

It works when the number of records is less but not working when the records grows.

Thanks Again

Last edited:

#### MajP

##### You've got your good things, and you've got mine.
To do this you need to do a group by ranking. In Access SQL you can do this with either a subquery or a dcount like @June7 did in the other thread.
Here is a simple group by ranking. Each athlete in a SchoolAssociation is ranked by name. First I built a qry for the 100 event that included the name and event data. Then used that below.

Code:
``````SELECT qry100.athleteid,
qry100.schoolassociation,
qry100.athletename,
(SELECT Count(*) + 1
FROM   qry100 AS B
WHERE  B.athletename < qry100.athletename
AND B.schoolassociation = qry100.schoolassociation) AS GroupRank
FROM   qry100
ORDER  BY qry100.schoolassociation,
qry100.athletename;``````

So you would get something like this.
Query3 Query3

AthleteIDSchoolAssociationAthleteNameGroupRank
556​
East Garo Hills District Sports AssociationANIMA ARENGH
1​
548​
East Garo Hills District Sports AssociationARAN CH MARAK
2​
559​
East Garo Hills District Sports AssociationCHANAKAM SANGMA
3​
552​
East Garo Hills District Sports AssociationDILCHANG N SANGMA
4​
614​
East Jaintia Hills District Athletic AssociationKYNTON SHYLLA
1​
615​
East Jaintia Hills District Athletic AssociationSHONGHOK DKHAR
2​
799​
Mawkyrwat District Sports AssociationBalapynkupbor Wahlang
1​
792​
Mawkyrwat District Sports AssociationBatskhemlang Thongnirit
2​
815​
Mawkyrwat District Sports AssociationHappymerry Lyngkhoi
3​
744​
Nongstoin District Sports AssociationAllabastar Dkhar
1​
739​
Nongstoin District Sports AssociationFillanjost Marngar
2​
765​
Nongstoin District Sports AssociationIbaihun Kurbah
3​
746​
Nongstoin District Sports AssociationMathius Shyrkon
4​
738​
Nongstoin District Sports AssociationSanlawei Jyndiang
5​
766​
Nongstoin District Sports AssociationSavinity Jyndiang
6​
745​
Nongstoin District Sports AssociationShaipharlin Nongsiej
7​
758​
Nongstoin District Sports AssociationTiewlari Rynshiang
8​
760​
Nongstoin District Sports AssociationWanda Lyngkhoi
9​
679​
North Garo Hills District AssociationBETTINGSTAR N. SANGMA
1​
685​
North Garo Hills District AssociationCHUMAN MARAK
2​
700​
North Garo Hills District AssociationNOJIM K. Marak
3​
701​
North Garo Hills District AssociationPATIAMA K. SANGMA
4​
684​
North Garo Hills District AssociationRAJA K. SANGMA
5​
680​
North Garo Hills District AssociationSANTHU K. SANGMA
6​
699​
North Garo Hills District AssociationSILTE G. MOMIN
7​
594​
Ri Bhoi District Sports AssociationDORALIN RONGHI
1​
593​
Ri Bhoi District Sports AssociationJOYTIFUL NARLONG
2​
582​
Ri Bhoi District Sports AssociationSHIDALIN RONGPI
3​
587​
Ri Bhoi District Sports AssociationTWINSTAR LYNGDOH
4​
851​
Shillong Sports AssociationAngelina Jackson
1​
846​
2​
825​
Shillong Sports AssociationDaman Warjri
3​
823​
Shillong Sports AssociationDamebansan Rymbai
4​
830​
Shillong Sports AssociationEderick Sohtun
5​
847​
Shillong Sports AssociationFeliscita Rynjah
6​
853​
Shillong Sports AssociationGabriela D Kharrubon
7​
831​
Shillong Sports AssociationKyntiewshaphrang Khongwir
8​
784​
South Garo Hills District Athletic AssociationAlbero R. Marak
1​
781​
South Garo Hills District Athletic AssociationParlee R. Marak
2​
782​
South Garo Hills District Athletic AssociationSalom S. Marak
3​
785​
South Garo Hills District Athletic AssociationSengrik T. Sangma
4​
646​
South West Garo Hills District AssociationBALDIM D SANGMA
1​
641​
South West Garo Hills District AssociationGRACEWIN CH MARAK
2​
672​
Tura District Athletic AssociationGRIMCHI CH. SANGMA
1​
654​
Tura District Athletic AssociationKADIL NOAH CHANDOLA B. SANGMA
2​
660​
Tura District Athletic AssociationKIMKIM CH. MOMIN
3​
656​
Tura District Athletic AssociationMATSRAM JAKRIKRA M. MARAK
4​
671​
Tura District Athletic AssociationMIRIME D. SANGMA
5​
670​
Tura District Athletic AssociationRINGRINGCHI K. SANGMA
6​
658​
Tura District Athletic AssociationTUEBIRTH CH. SANGMA
7​
675​
Tura District Athletic AssociationWANMERA A. SANGMA
8​
730​
West Jaintia Hills District Sports Association BEAUTIFY SHYLLA
1​
714​
West Jaintia Hills District Sports Association CHIBAIT DON HI SHYLLA
2​
709​
West Jaintia Hills District Sports Association DAMAN SARI
3​
731​
West Jaintia Hills District Sports Association DAMIKA R LYNGDOH
4​
729​
West Jaintia Hills District Sports Association IAISHAH DKHAR
5​
708​
West Jaintia Hills District Sports Association IOHIKSAN LYNGDOH
6​
713​
West Jaintia Hills District Sports Association SUNIDABIANG YMBON
7​

#### MajP

##### You've got your good things, and you've got mine.
Then sort the above query by rank, school association, name you get
Query3 Query3

AthleteIDSchoolAssociationAthleteNameGroupRank
556​
East Garo Hills District Sports AssociationANIMA ARENGH
1​
614​
East Jaintia Hills District Athletic AssociationKYNTON SHYLLA
1​
799​
Mawkyrwat District Sports AssociationBalapynkupbor Wahlang
1​
744​
Nongstoin District Sports AssociationAllabastar Dkhar
1​
679​
North Garo Hills District AssociationBETTINGSTAR N. SANGMA
1​
594​
Ri Bhoi District Sports AssociationDORALIN RONGHI
1​
851​
Shillong Sports AssociationAngelina Jackson
1​
784​
South Garo Hills District Athletic AssociationAlbero R. Marak
1​
646​
South West Garo Hills District AssociationBALDIM D SANGMA
1​
672​
Tura District Athletic AssociationGRIMCHI CH. SANGMA
1​
730​
West Jaintia Hills District Sports Association BEAUTIFY SHYLLA
1​
548​
East Garo Hills District Sports AssociationARAN CH MARAK
2​
615​
East Jaintia Hills District Athletic AssociationSHONGHOK DKHAR
2​
792​
Mawkyrwat District Sports AssociationBatskhemlang Thongnirit
2​
739​
Nongstoin District Sports AssociationFillanjost Marngar
2​
685​
North Garo Hills District AssociationCHUMAN MARAK
2​
593​
Ri Bhoi District Sports AssociationJOYTIFUL NARLONG
2​
846​
2​
781​
South Garo Hills District Athletic AssociationParlee R. Marak
2​
641​
South West Garo Hills District AssociationGRACEWIN CH MARAK
2​
654​
Tura District Athletic AssociationKADIL NOAH CHANDOLA B. SANGMA
2​
714​
West Jaintia Hills District Sports Association CHIBAIT DON HI SHYLLA
2​
559​
East Garo Hills District Sports AssociationCHANAKAM SANGMA
3​
815​
Mawkyrwat District Sports AssociationHappymerry Lyngkhoi
3​
765​
Nongstoin District Sports AssociationIbaihun Kurbah
3​
700​
North Garo Hills District AssociationNOJIM K. Marak
3​
582​
Ri Bhoi District Sports AssociationSHIDALIN RONGPI
3​
825​
Shillong Sports AssociationDaman Warjri
3​
782​
South Garo Hills District Athletic AssociationSalom S. Marak
3​
660​
Tura District Athletic AssociationKIMKIM CH. MOMIN
3​
709​
West Jaintia Hills District Sports Association DAMAN SARI
3​
552​
East Garo Hills District Sports AssociationDILCHANG N SANGMA
4​
746​
Nongstoin District Sports AssociationMathius Shyrkon
4​
701​
North Garo Hills District AssociationPATIAMA K. SANGMA
4​
587​
Ri Bhoi District Sports AssociationTWINSTAR LYNGDOH
4​
823​
Shillong Sports AssociationDamebansan Rymbai
4​
785​
South Garo Hills District Athletic AssociationSengrik T. Sangma
4​
656​
Tura District Athletic AssociationMATSRAM JAKRIKRA M. MARAK
4​
731​
West Jaintia Hills District Sports Association DAMIKA R LYNGDOH
4​
738​
Nongstoin District Sports AssociationSanlawei Jyndiang
5​
684​
North Garo Hills District AssociationRAJA K. SANGMA
5​
830​
Shillong Sports AssociationEderick Sohtun
5​
671​
Tura District Athletic AssociationMIRIME D. SANGMA
5​
729​
West Jaintia Hills District Sports Association IAISHAH DKHAR
5​
766​
Nongstoin District Sports AssociationSavinity Jyndiang
6​
680​
North Garo Hills District AssociationSANTHU K. SANGMA
6​
847​
Shillong Sports AssociationFeliscita Rynjah
6​
670​
Tura District Athletic AssociationRINGRINGCHI K. SANGMA
6​
708​
West Jaintia Hills District Sports Association IOHIKSAN LYNGDOH
6​
745​
Nongstoin District Sports AssociationShaipharlin Nongsiej
7​
699​
North Garo Hills District AssociationSILTE G. MOMIN
7​
853​
Shillong Sports AssociationGabriela D Kharrubon
7​
658​
Tura District Athletic AssociationTUEBIRTH CH. SANGMA
7​
713​
West Jaintia Hills District Sports Association SUNIDABIANG YMBON
7​
758​
Nongstoin District Sports AssociationTiewlari Rynshiang
8​
831​
Shillong Sports AssociationKyntiewshaphrang Khongwir
8​
675​
Tura District Athletic AssociationWANMERA A. SANGMA
8​
760​
Nongstoin District Sports AssociationWanda Lyngkhoi
9​

#### MajP

##### You've got your good things, and you've got mine.
Thanks for your support but for now I really need just the sorting logic. if my data is in the following order
FYI. instead of speaking in CAPS. You could stop wasting everyone's time and spend a few minutes on your own crafting a good question with proper details. It took a lot of really smart people a lot of time, to have any clue what you are asking. YOU NEED TO PUT IN SOME EFFORT if you want a proper answer. We are not mind readers, just really good at Access..

#### amirkharkongor

##### Member
FYI. instead of speaking in CAPS. You could stop wasting everyone's time and spend a few minutes on your own crafting a good question with proper details. It took a lot of really smart people a lot of time, to have any clue what you are asking. YOU NEED TO PUT IN SOME EFFORT if you want a proper answer. We are not mind readers, just really good at Access..
I am really sorry. I did not mean to disrespect you all. I just want to highlight what I want.

Sorry again

#### The_Doc_Man

##### Immoderate Moderator
Staff member
MajP was correct in complaining but don't take it to mean that you are going to be cut off from help. The lesson, if there is one, is that a better-formed question will get a better, quicker answer. Instead of having us draw out the rules and the real scenario, you would have gotten a much quicker response if you had told us ALL of the constraints and goals at once. In that sense, this was still a good exercise - but not only for you. Any others who read this would recognize that a good question is more likely to get a good answer and an incomplete question is more likely to get an incomplete answer.

#### amirkharkongor

##### Member
To do this you need to do a group by ranking. In Access SQL you can do this with either a subquery or a dcount like @June7 did in the other thread.
Here is a simple group by ranking. Each athlete in a SchoolAssociation is ranked by name. First I built a qry for the 100 event that included the name and event data. Then used that below.

Code:
``````SELECT qry100.athleteid,
qry100.schoolassociation,
qry100.athletename,
(SELECT Count(*) + 1
FROM   qry100 AS B
WHERE  B.athletename < qry100.athletename
AND B.schoolassociation = qry100.schoolassociation) AS GroupRank
FROM   qry100
ORDER  BY qry100.schoolassociation,
qry100.athletename;``````

So you would get something like this.
Query3 Query3

AthleteIDSchoolAssociationAthleteNameGroupRank
556​
East Garo Hills District Sports AssociationANIMA ARENGH
1​
548​
East Garo Hills District Sports AssociationARAN CH MARAK
2​
559​
East Garo Hills District Sports AssociationCHANAKAM SANGMA
3​
552​
East Garo Hills District Sports AssociationDILCHANG N SANGMA
4​
614​
East Jaintia Hills District Athletic AssociationKYNTON SHYLLA
1​
615​
East Jaintia Hills District Athletic AssociationSHONGHOK DKHAR
2​
799​
Mawkyrwat District Sports AssociationBalapynkupbor Wahlang
1​
792​
Mawkyrwat District Sports AssociationBatskhemlang Thongnirit
2​
815​
Mawkyrwat District Sports AssociationHappymerry Lyngkhoi
3​
744​
Nongstoin District Sports AssociationAllabastar Dkhar
1​
739​
Nongstoin District Sports AssociationFillanjost Marngar
2​
765​
Nongstoin District Sports AssociationIbaihun Kurbah
3​
746​
Nongstoin District Sports AssociationMathius Shyrkon
4​
738​
Nongstoin District Sports AssociationSanlawei Jyndiang
5​
766​
Nongstoin District Sports AssociationSavinity Jyndiang
6​
745​
Nongstoin District Sports AssociationShaipharlin Nongsiej
7​
758​
Nongstoin District Sports AssociationTiewlari Rynshiang
8​
760​
Nongstoin District Sports AssociationWanda Lyngkhoi
9​
679​
North Garo Hills District AssociationBETTINGSTAR N. SANGMA
1​
685​
North Garo Hills District AssociationCHUMAN MARAK
2​
700​
North Garo Hills District AssociationNOJIM K. Marak
3​
701​
North Garo Hills District AssociationPATIAMA K. SANGMA
4​
684​
North Garo Hills District AssociationRAJA K. SANGMA
5​
680​
North Garo Hills District AssociationSANTHU K. SANGMA
6​
699​
North Garo Hills District AssociationSILTE G. MOMIN
7​
594​
Ri Bhoi District Sports AssociationDORALIN RONGHI
1​
593​
Ri Bhoi District Sports AssociationJOYTIFUL NARLONG
2​
582​
Ri Bhoi District Sports AssociationSHIDALIN RONGPI
3​
587​
Ri Bhoi District Sports AssociationTWINSTAR LYNGDOH
4​
851​
Shillong Sports AssociationAngelina Jackson
1​
846​
2​
825​
Shillong Sports AssociationDaman Warjri
3​
823​
Shillong Sports AssociationDamebansan Rymbai
4​
830​
Shillong Sports AssociationEderick Sohtun
5​
847​
Shillong Sports AssociationFeliscita Rynjah
6​
853​
Shillong Sports AssociationGabriela D Kharrubon
7​
831​
Shillong Sports AssociationKyntiewshaphrang Khongwir
8​
784​
South Garo Hills District Athletic AssociationAlbero R. Marak
1​
781​
South Garo Hills District Athletic AssociationParlee R. Marak
2​
782​
South Garo Hills District Athletic AssociationSalom S. Marak
3​
785​
South Garo Hills District Athletic AssociationSengrik T. Sangma
4​
646​
South West Garo Hills District AssociationBALDIM D SANGMA
1​
641​
South West Garo Hills District AssociationGRACEWIN CH MARAK
2​
672​
Tura District Athletic AssociationGRIMCHI CH. SANGMA
1​
654​
Tura District Athletic AssociationKADIL NOAH CHANDOLA B. SANGMA
2​
660​
Tura District Athletic AssociationKIMKIM CH. MOMIN
3​
656​
Tura District Athletic AssociationMATSRAM JAKRIKRA M. MARAK
4​
671​
Tura District Athletic AssociationMIRIME D. SANGMA
5​
670​
Tura District Athletic AssociationRINGRINGCHI K. SANGMA
6​
658​
Tura District Athletic AssociationTUEBIRTH CH. SANGMA
7​
675​
Tura District Athletic AssociationWANMERA A. SANGMA
8​
730​
West Jaintia Hills District Sports AssociationBEAUTIFY SHYLLA
1​
714​
West Jaintia Hills District Sports AssociationCHIBAIT DON HI SHYLLA
2​
709​
West Jaintia Hills District Sports AssociationDAMAN SARI
3​
731​
West Jaintia Hills District Sports AssociationDAMIKA R LYNGDOH
4​
729​
West Jaintia Hills District Sports AssociationIAISHAH DKHAR
5​
708​
West Jaintia Hills District Sports AssociationIOHIKSAN LYNGDOH
6​
713​
West Jaintia Hills District Sports AssociationSUNIDABIANG YMBON
7​
Hi,

I have used your GroupRank Query (Query2) on the Filtered Query (Query1) and it is working fine.

I tried to use your GroupRank Query (Query without Filter) directly on the on the Table AthleteEvent, it is also working fine and I am getting my desired sorting but When I tried to use GroupRank Query along with Filter (Query with Filter) then I am having problem and I am not getting the desired output.

Is there anyway that I can use GroupRank Query with Filter directly on my Table.

#### Attachments

• Dummy.accdb
3.4 MB · Views: 6
Last edited:

#### amirkharkongor

##### Member
Hi,

I have used your GroupRank Query (Query2) on the Filtered Query (Query1) and it is working fine.

I tried to use your GroupRank Query (Query without Filter) directly on the on the Table AthleteEvent, it is also working fine and I am getting my desired sorting but When I tried to use GroupRank Query along with Filter (Query with Filter) then I am having problem and I am not getting the desired output.

Is there anyway that I can use GroupRank Query with Filter directly on my Table.

I also found that even if use the GroupRank Query (Query2) on the Filtered Query (Query1), the results of the Query2 is also not editable.

#### MajP

##### You've got your good things, and you've got mine.
but When I tried to use GroupRank Query along with Filter (Query with Filter) then I am having problem and I am not getting the desired output.
You need to think about that. If you establish the groups based on an ufilter set and apply that to a filtered set you are going to get nonsensical results.

This is what you tried.
Code:
``````SELECT (SELECT Count(*) + 1
FROM   athleteevent AS B
WHERE  B.eventid < athleteevent.eventid
AND B.schoolassociation = athleteevent.schoolassociation) AS
GroupRank,
athletedata.bibno,
athletedata.athletename,
athleteevent.schoolassociation,
athletedata.category,
athletedata.gender,
athleteevent.event,
athleteevent.heats,
athleteevent.heatslaneno,
athleteevent.heatsscore,
athleteevent.finalist,
athleteevent.finalslaneno,
athleteevent.finalsscore,
athleteevent.remarks,
athleteevent.medaltype,
athleteevent.finalremarks,
athleteevent.finalposition
FROM   athletedata
INNER JOIN athleteevent
ON athletedata.athleteid = athleteevent.athleteid
WHERE  ( ( ( athletedata.category ) = "under - 18" )
AND ( ( athletedata.gender ) = "boys" )
AND ( ( athleteevent.event ) = "100m" ) )
ORDER  BY athleteevent.schoolassociation;``````

The sub query has to be based on the same group of records. So I would first make query qryBoysUnder18_100M.
Then Do something like
Code:
``````SELECT (SELECT Count(*) + 1
FROM   qryboysunder18_100m AS B
WHERE  B.athletename < qryboysunder18_100m.athletename
AND B.event = qryboysunder18_100m.event
AND B.schoolassociation = qryboysunder18_100m.schoolassociation)
AS
GroupRank,
qryboysunder18_100m.bibno,
qryboysunder18_100m.athletename,
qryboysunder18_100m.schoolassociation,
qryboysunder18_100m.category,
qryboysunder18_100m.gender,
qryboysunder18_100m.event,
qryboysunder18_100m.heats,
qryboysunder18_100m.heatslaneno,
qryboysunder18_100m.heatsscore,
qryboysunder18_100m.finalist,
qryboysunder18_100m.finalslaneno,
qryboysunder18_100m.finalsscore,
qryboysunder18_100m.remarks,
qryboysunder18_100m.medaltype,
qryboysunder18_100m.finalremarks,
qryboysunder18_100m.finalposition
FROM   qryboysunder18_100m
ORDER  BY qryboysunder18_100m.schoolassociation;``````

So some reason this query does not sort on rank so I needed a third query to do that. Since they use aggregate and subqueries they are not updateable.

#### amirkharkongor

##### Member
You need to think about that. If you establish the groups based on an ufilter set and apply that to a filtered set you are going to get nonsensical results.

This is what you tried.
Code:
``````SELECT (SELECT Count(*) + 1
FROM   athleteevent AS B
WHERE  B.eventid < athleteevent.eventid
AND B.schoolassociation = athleteevent.schoolassociation) AS
GroupRank,
athletedata.bibno,
athletedata.athletename,
athleteevent.schoolassociation,
athletedata.category,
athletedata.gender,
athleteevent.event,
athleteevent.heats,
athleteevent.heatslaneno,
athleteevent.heatsscore,
athleteevent.finalist,
athleteevent.finalslaneno,
athleteevent.finalsscore,
athleteevent.remarks,
athleteevent.medaltype,
athleteevent.finalremarks,
athleteevent.finalposition
FROM   athletedata
INNER JOIN athleteevent
ON athletedata.athleteid = athleteevent.athleteid
WHERE  ( ( ( athletedata.category ) = "under - 18" )
AND ( ( athletedata.gender ) = "boys" )
AND ( ( athleteevent.event ) = "100m" ) )
ORDER  BY athleteevent.schoolassociation;``````

The sub query has to be based on the same group of records. So I would first make query qryBoysUnder18_100M.
Then Do something like
Code:
``````SELECT (SELECT Count(*) + 1
FROM   qryboysunder18_100m AS B
WHERE  B.athletename < qryboysunder18_100m.athletename
AND B.event = qryboysunder18_100m.event
AND B.schoolassociation = qryboysunder18_100m.schoolassociation)
AS
GroupRank,
qryboysunder18_100m.bibno,
qryboysunder18_100m.athletename,
qryboysunder18_100m.schoolassociation,
qryboysunder18_100m.category,
qryboysunder18_100m.gender,
qryboysunder18_100m.event,
qryboysunder18_100m.heats,
qryboysunder18_100m.heatslaneno,
qryboysunder18_100m.heatsscore,
qryboysunder18_100m.finalist,
qryboysunder18_100m.finalslaneno,
qryboysunder18_100m.finalsscore,
qryboysunder18_100m.remarks,
qryboysunder18_100m.medaltype,
qryboysunder18_100m.finalremarks,
qryboysunder18_100m.finalposition
FROM   qryboysunder18_100m
ORDER  BY qryboysunder18_100m.schoolassociation;``````

So some reason this query does not sort on rank so I needed a third query to do that. Since they use aggregate and subqueries they are not updateable.
Thanks for taking time to find a solution for me.

If possible, can you find out any other way to make this type of query updateable

#### MajP

##### You've got your good things, and you've got mine.
If you have to do a lot of Sorts for a lot of events this could simplify. This should be updateable. However, you will likely always have to make an "Event" query first. Then use that to get you special sort.

Code:
``````Public Function RankInSchoolGroup(QueryName As String, TheAthleteName As String, SchoolAssociation As String, EventName As String) As Integer
'requires a query that includes SchoolAssociation, Event, and AthleteName.  This will rank them alphabetically in the School
Dim criteria As String
criteria = "SchoolAssociation = '" & Replace(SchoolAssociation, "'", "''") & "'" & " AND Event = '" & EventName & "'" & " AND AthleteName <= '" & TheAthleteName & "'"
RankInSchoolGroup = DCount("*", QueryName, criteria)
End Function``````

Now you can more simply build your event queries. Then call this function from that query and pass in the name of the query, and the fields that hold the athletename, schoolassociation,Event.

Code:
``````SELECT qrygirls4x100.athleteid,
qrygirls4x100.bibno,
qrygirls4x100.athletename,
qrygirls4x100.gender,
Rankinschoolgroup("qrygirls4x100", [athletename], [schoolassociation],
[event])
AS RankInGroup,
qrygirls4x100.schoolassociation,
qrygirls4x100.event
FROM   qrygirls4x100
ORDER  BY Rankinschoolgroup("qrygirls4x100", [athletename], [schoolassociation],
[event]),
qrygirls4x100.schoolassociation;``````

Looked at the qirls 4x100 Grouped query

#### Attachments

• Dummy2.accdb
3.4 MB · Views: 0

#### MajP

##### You've got your good things, and you've got mine.
Can you better describe your process of how you would use this? Event if you get this sort I would think you need to save the results somewhere.

From what I am guessing you have to create a lot of Events defined by Age, Sex, Event Type, (maybe others)
Ex.
Boys, Under 18, 100 M
Girls, Under 16, 4x100M

For each race you will have to use this special sorts for the purpose of putting runners into heats so that athletes from the same schools are not in the same heats and creating the "races"

If that is the case I would expect to see another table with Specific Events, and a junction table with athletes, specific event, Heat assignment,
The current table AthleteEvent does not have the specific "Event" as far as I can tell.