Special Sort

MajP · May 13, 2022

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

SchoolAssociation	CountOfAthleteID
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 · May 13, 2022

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 · May 13, 2022

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 · May 14, 2022

Hi Friends,

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

BIbNo	AthleteName	SchoolAssociation
046	ANIMA ARENGH	East Garo Hills District Sports Association
248	Wanda Lyngkhoi	Nongstoin District Sports Association
246	Tiewlari Rynshiang	Nongstoin District Sports Association
190	SILTE G. MOMIN	North Garo Hills District Association
073	SHIDALIN RONGPI	Ri Bhoi District Sports Association
025	Feliscita Rynjah	Shillong Sports Association
024	Arkador Lyngdoh Nongbri	Shillong Sports Association
162	MIRIME D. SANGMA	Tura District Athletic Association
161	RINGRINGCHI K. SANGMA	Tura District Athletic Association
320	BEAUTIFY SHYLLA	West Jaintia Hills District Sports Association
220	IAISHAH DKHAR	West Jaintia Hills District Sports Association

I just want to sort the SchoolAssociation in the following order

BIbNo	AthleteName	SchoolAssociation
046	ANIMA ARENGH	East Garo Hills District Sports Association
248	Wanda Lyngkhoi	Nongstoin District Sports Association
190	SILTE G. MOMIN	North Garo Hills District Association
073	SHIDALIN RONGPI	Ri Bhoi District Sports Association
025	Feliscita Rynjah	Shillong Sports Association
162	MIRIME D. SANGMA	Tura District Athletic Association
320	BEAUTIFY SHYLLA	West Jaintia Hills District Sports Association
246	Tiewlari Rynshiang	Nongstoin District Sports Association
024	Arkador Lyngdoh Nongbri	Shillong Sports Association
161	RINGRINGCHI K. SANGMA	Tura District Athletic Association
220	IAISHAH DKHAR	West Jaintia Hills District Sports Association

I tried the solution provided in

Custom Sort for sorting records like aaabbbccc to abcabcabc

Hi everyone, I am new to Access Programming. I am having data as a a a b b b c c c Now I want to sort the sort the record as a b c a b c a b c Anyone kindly hep me. Thanks Amir Kharkongor

www.access-programmers.co.uk

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

Thanks Again

MajP · May 14, 2022

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

AthleteID	SchoolAssociation	AthleteName	GroupRank
556	East Garo Hills District Sports Association	ANIMA ARENGH	1
548	East Garo Hills District Sports Association	ARAN CH MARAK	2
559	East Garo Hills District Sports Association	CHANAKAM SANGMA	3
552	East Garo Hills District Sports Association	DILCHANG N SANGMA	4
614	East Jaintia Hills District Athletic Association	KYNTON SHYLLA	1
615	East Jaintia Hills District Athletic Association	SHONGHOK DKHAR	2
799	Mawkyrwat District Sports Association	Balapynkupbor Wahlang	1
792	Mawkyrwat District Sports Association	Batskhemlang Thongnirit	2
815	Mawkyrwat District Sports Association	Happymerry Lyngkhoi	3
744	Nongstoin District Sports Association	Allabastar Dkhar	1
739	Nongstoin District Sports Association	Fillanjost Marngar	2
765	Nongstoin District Sports Association	Ibaihun Kurbah	3
746	Nongstoin District Sports Association	Mathius Shyrkon	4
738	Nongstoin District Sports Association	Sanlawei Jyndiang	5
766	Nongstoin District Sports Association	Savinity Jyndiang	6
745	Nongstoin District Sports Association	Shaipharlin Nongsiej	7
758	Nongstoin District Sports Association	Tiewlari Rynshiang	8
760	Nongstoin District Sports Association	Wanda Lyngkhoi	9
679	North Garo Hills District Association	BETTINGSTAR N. SANGMA	1
685	North Garo Hills District Association	CHUMAN MARAK	2
700	North Garo Hills District Association	NOJIM K. Marak	3
701	North Garo Hills District Association	PATIAMA K. SANGMA	4
684	North Garo Hills District Association	RAJA K. SANGMA	5
680	North Garo Hills District Association	SANTHU K. SANGMA	6
699	North Garo Hills District Association	SILTE G. MOMIN	7
594	Ri Bhoi District Sports Association	DORALIN RONGHI	1
593	Ri Bhoi District Sports Association	JOYTIFUL NARLONG	2
582	Ri Bhoi District Sports Association	SHIDALIN RONGPI	3
587	Ri Bhoi District Sports Association	TWINSTAR LYNGDOH	4
851	Shillong Sports Association	Angelina Jackson	1
846	Shillong Sports Association	Arkador Lyngdoh Nongbri	2
825	Shillong Sports Association	Daman Warjri	3
823	Shillong Sports Association	Damebansan Rymbai	4
830	Shillong Sports Association	Ederick Sohtun	5
847	Shillong Sports Association	Feliscita Rynjah	6
853	Shillong Sports Association	Gabriela D Kharrubon	7
831	Shillong Sports Association	Kyntiewshaphrang Khongwir	8
784	South Garo Hills District Athletic Association	Albero R. Marak	1
781	South Garo Hills District Athletic Association	Parlee R. Marak	2
782	South Garo Hills District Athletic Association	Salom S. Marak	3
785	South Garo Hills District Athletic Association	Sengrik T. Sangma	4
646	South West Garo Hills District Association	BALDIM D SANGMA	1
641	South West Garo Hills District Association	GRACEWIN CH MARAK	2
672	Tura District Athletic Association	GRIMCHI CH. SANGMA	1
654	Tura District Athletic Association	KADIL NOAH CHANDOLA B. SANGMA	2
660	Tura District Athletic Association	KIMKIM CH. MOMIN	3
656	Tura District Athletic Association	MATSRAM JAKRIKRA M. MARAK	4
671	Tura District Athletic Association	MIRIME D. SANGMA	5
670	Tura District Athletic Association	RINGRINGCHI K. SANGMA	6
658	Tura District Athletic Association	TUEBIRTH CH. SANGMA	7
675	Tura District Athletic Association	WANMERA 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 · May 14, 2022

Then sort the above query by rank, school association, name you get
Query3 Query3

AthleteID	SchoolAssociation	AthleteName	GroupRank
556	East Garo Hills District Sports Association	ANIMA ARENGH	1
614	East Jaintia Hills District Athletic Association	KYNTON SHYLLA	1
799	Mawkyrwat District Sports Association	Balapynkupbor Wahlang	1
744	Nongstoin District Sports Association	Allabastar Dkhar	1
679	North Garo Hills District Association	BETTINGSTAR N. SANGMA	1
594	Ri Bhoi District Sports Association	DORALIN RONGHI	1
851	Shillong Sports Association	Angelina Jackson	1
784	South Garo Hills District Athletic Association	Albero R. Marak	1
646	South West Garo Hills District Association	BALDIM D SANGMA	1
672	Tura District Athletic Association	GRIMCHI CH. SANGMA	1
730	West Jaintia Hills District Sports Association	BEAUTIFY SHYLLA	1
548	East Garo Hills District Sports Association	ARAN CH MARAK	2
615	East Jaintia Hills District Athletic Association	SHONGHOK DKHAR	2
792	Mawkyrwat District Sports Association	Batskhemlang Thongnirit	2
739	Nongstoin District Sports Association	Fillanjost Marngar	2
685	North Garo Hills District Association	CHUMAN MARAK	2
593	Ri Bhoi District Sports Association	JOYTIFUL NARLONG	2
846	Shillong Sports Association	Arkador Lyngdoh Nongbri	2
781	South Garo Hills District Athletic Association	Parlee R. Marak	2
641	South West Garo Hills District Association	GRACEWIN CH MARAK	2
654	Tura District Athletic Association	KADIL NOAH CHANDOLA B. SANGMA	2
714	West Jaintia Hills District Sports Association	CHIBAIT DON HI SHYLLA	2
559	East Garo Hills District Sports Association	CHANAKAM SANGMA	3
815	Mawkyrwat District Sports Association	Happymerry Lyngkhoi	3
765	Nongstoin District Sports Association	Ibaihun Kurbah	3
700	North Garo Hills District Association	NOJIM K. Marak	3
582	Ri Bhoi District Sports Association	SHIDALIN RONGPI	3
825	Shillong Sports Association	Daman Warjri	3
782	South Garo Hills District Athletic Association	Salom S. Marak	3
660	Tura District Athletic Association	KIMKIM CH. MOMIN	3
709	West Jaintia Hills District Sports Association	DAMAN SARI	3
552	East Garo Hills District Sports Association	DILCHANG N SANGMA	4
746	Nongstoin District Sports Association	Mathius Shyrkon	4
701	North Garo Hills District Association	PATIAMA K. SANGMA	4
587	Ri Bhoi District Sports Association	TWINSTAR LYNGDOH	4
823	Shillong Sports Association	Damebansan Rymbai	4
785	South Garo Hills District Athletic Association	Sengrik T. Sangma	4
656	Tura District Athletic Association	MATSRAM JAKRIKRA M. MARAK	4
731	West Jaintia Hills District Sports Association	DAMIKA R LYNGDOH	4
738	Nongstoin District Sports Association	Sanlawei Jyndiang	5
684	North Garo Hills District Association	RAJA K. SANGMA	5
830	Shillong Sports Association	Ederick Sohtun	5
671	Tura District Athletic Association	MIRIME D. SANGMA	5
729	West Jaintia Hills District Sports Association	IAISHAH DKHAR	5
766	Nongstoin District Sports Association	Savinity Jyndiang	6
680	North Garo Hills District Association	SANTHU K. SANGMA	6
847	Shillong Sports Association	Feliscita Rynjah	6
670	Tura District Athletic Association	RINGRINGCHI K. SANGMA	6
708	West Jaintia Hills District Sports Association	IOHIKSAN LYNGDOH	6
745	Nongstoin District Sports Association	Shaipharlin Nongsiej	7
699	North Garo Hills District Association	SILTE G. MOMIN	7
853	Shillong Sports Association	Gabriela D Kharrubon	7
658	Tura District Athletic Association	TUEBIRTH CH. SANGMA	7
713	West Jaintia Hills District Sports Association	SUNIDABIANG YMBON	7
758	Nongstoin District Sports Association	Tiewlari Rynshiang	8
831	Shillong Sports Association	Kyntiewshaphrang Khongwir	8
675	Tura District Athletic Association	WANMERA A. SANGMA	8
760	Nongstoin District Sports Association	Wanda Lyngkhoi	9

MajP · May 14, 2022

amirkharkongor said:
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 · May 14, 2022

MajP said:
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 · May 14, 2022

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 · May 24, 2022

MajP said:
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

AthleteID SchoolAssociation AthleteName GroupRank
556
East Garo Hills District Sports Association ANIMA ARENGH
1
548
East Garo Hills District Sports Association ARAN CH MARAK
2
559
East Garo Hills District Sports Association CHANAKAM SANGMA
3
552
East Garo Hills District Sports Association DILCHANG N SANGMA
4
614
East Jaintia Hills District Athletic Association KYNTON SHYLLA
1
615
East Jaintia Hills District Athletic Association SHONGHOK DKHAR
2
799
Mawkyrwat District Sports Association Balapynkupbor Wahlang
1
792
Mawkyrwat District Sports Association Batskhemlang Thongnirit
2
815
Mawkyrwat District Sports Association Happymerry Lyngkhoi
3
744
Nongstoin District Sports Association Allabastar Dkhar
1
739
Nongstoin District Sports Association Fillanjost Marngar
2
765
Nongstoin District Sports Association Ibaihun Kurbah
3
746
Nongstoin District Sports Association Mathius Shyrkon
4
738
Nongstoin District Sports Association Sanlawei Jyndiang
5
766
Nongstoin District Sports Association Savinity Jyndiang
6
745
Nongstoin District Sports Association Shaipharlin Nongsiej
7
758
Nongstoin District Sports Association Tiewlari Rynshiang
8
760
Nongstoin District Sports Association Wanda Lyngkhoi
9
679
North Garo Hills District Association BETTINGSTAR N. SANGMA
1
685
North Garo Hills District Association CHUMAN MARAK
2
700
North Garo Hills District Association NOJIM K. Marak
3
701
North Garo Hills District Association PATIAMA K. SANGMA
4
684
North Garo Hills District Association RAJA K. SANGMA
5
680
North Garo Hills District Association SANTHU K. SANGMA
6
699
North Garo Hills District Association SILTE G. MOMIN
7
594
Ri Bhoi District Sports Association DORALIN RONGHI
1
593
Ri Bhoi District Sports Association JOYTIFUL NARLONG
2
582
Ri Bhoi District Sports Association SHIDALIN RONGPI
3
587
Ri Bhoi District Sports Association TWINSTAR LYNGDOH
4
851
Shillong Sports Association Angelina Jackson
1
846
Shillong Sports Association Arkador Lyngdoh Nongbri
2
825
Shillong Sports Association Daman Warjri
3
823
Shillong Sports Association Damebansan Rymbai
4
830
Shillong Sports Association Ederick Sohtun
5
847
Shillong Sports Association Feliscita Rynjah
6
853
Shillong Sports Association Gabriela D Kharrubon
7
831
Shillong Sports Association Kyntiewshaphrang Khongwir
8
784
South Garo Hills District Athletic Association Albero R. Marak
1
781
South Garo Hills District Athletic Association Parlee R. Marak
2
782
South Garo Hills District Athletic Association Salom S. Marak
3
785
South Garo Hills District Athletic Association Sengrik T. Sangma
4
646
South West Garo Hills District Association BALDIM D SANGMA
1
641
South West Garo Hills District Association GRACEWIN CH MARAK
2
672
Tura District Athletic Association GRIMCHI CH. SANGMA
1
654
Tura District Athletic Association KADIL NOAH CHANDOLA B. SANGMA
2
660
Tura District Athletic Association KIMKIM CH. MOMIN
3
656
Tura District Athletic Association MATSRAM JAKRIKRA M. MARAK
4
671
Tura District Athletic Association MIRIME D. SANGMA
5
670
Tura District Athletic Association RINGRINGCHI K. SANGMA
6
658
Tura District Athletic Association TUEBIRTH CH. SANGMA
7
675
Tura District Athletic Association WANMERA 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

Hi,

Please see Uploaded Dummy Database

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.

Thanks Please help me

amirkharkongor · May 26, 2022

amirkharkongor said:
Hi,

Please see Uploaded Dummy Database

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.

Thanks Please help me

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.

Thanks and Please help me.

MajP · May 26, 2022

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 · May 26, 2022

MajP said:

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 · May 26, 2022

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

MajP · May 26, 2022

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.

amirkharkongor · May 27, 2022

MajP said:
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.

Thank you very much for helping me and again sorry for earlier.

I have so much to learn from all of you.

Special Sort

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

Cronk

Registered User.

amirkharkongor

Member

Custom Sort for sorting records like aaabbbccc to abcabcabc

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

MajP

You've got your good things, and you've got mine.

amirkharkongor

Member

The_Doc_Man

Immoderate Moderator

amirkharkongor

Member

Attachments

amirkharkongor

Member

MajP

You've got your good things, and you've got mine.

amirkharkongor

Member

MajP

You've got your good things, and you've got mine.

Attachments

MajP

You've got your good things, and you've got mine.

amirkharkongor

Member

Users who are viewing this thread