Top 4 Query

[carlton123]

I'm looking to get the top four Members in each race from this Data

Race Member Position 1 GWA 1 1 GWA 2 1 RJC 3 1 GWA 4 1 TE 5 1 IW 6 1 PY 7 1 RJC 8 1 PJ 9 1 GT 10 2 RJC 1 2 GWA 2 2 RJC 3 2 GWA 4 2 PY 5 2 TE 6 2 TE 7 2 RJC 8 2 PJ 9 2 GT 10 3 GT 1 3 GT 2 3 RJC 3 3 PJ 4 3 IW 5 3 TE 6 3 TE 7 3 RJC 8 3 PJ 9 3 GT 10

to return something like Below I have added an extra column which is what I'm aiming to achieve eventually

Race Member Position Member Position 1 GWA 1 1 1 RJC 3 2 1 TE 5 3 1 IW 6 4 2 RJC 1 1 2 GWA 2 2 2 PY 5 3 2 TE 6 4 3 GT 1 1 3 RJC 3 2 3 PJ 4 3 3 IW 5 4

 

[jdraw]

Looks like you have a table structure problem at first glance. Tell us more about your table(s).

 

[carlton123]

table structure problem? im not sure what you mean its not exactly as it is i just simplified it when i pasted it it was in columns and its showing in a line now. basically its a results database the table holds RACE,MEMBER,POSITION other things that are not required for query

I want the top 4 members from each race ideally in order with another calculated field with 1 for top member 2 ,3 ,4 etc

thank you for looking

 

[Brianwarnock]

I don't understand your data, nor how position differs from your calculated field, but to get the top 4 in each race group on race, member and position, drag position in to the design grid again and select Where in the Totals drop down and add criteria <5

Brian

 

[carlton123]

Sorry i find it hard to explain myself

a member can have more than one position in each race so one member could come 1,2,3,4 so the member who cam position 5 would need to be member number 2

 

[Brianwarnock]

Oh! That kinda changes things

Brian

 

[carlton123]

Will it be possible? If i group on member and race then i just need to have a custom field that then puts a position next to member that's what I'm struggling with

 

[BlueIshDan]

Honestly its just pure lazyness to not format your data so that it is understandable enough for us to help you.

Fill in the blanks please...

[field1, field2, field3]
1 GWA 1
1 GWA 2
1 RJC 3
1 GWA 4
1 TE 5
1 IW 6
1 PY 7
1 RJC 8
1 PJ 9
1 GT 10
2 RJC 1
2 GWA 2
2 RJC 3
2 GWA 4
2 PY 5
2 TE 6
2 TE 7
2 RJC 8
2 PJ 9
2 GT 10
3 GT 1
3 GT 2
3 RJC 3
3 PJ 4
3 IW 5
3 TE 6
3 TE 7
3 RJC 8
3 PJ 9
3 GT 10

 

[carlton123]

Honestly its just pure lazyness to not format your data so that it is understandable enough for us to help you.

Fill in the blanks please...

[field1, field2, field3]
1 GWA 1
1 GWA 2
1 RJC 3
1 GWA 4
1 TE 5
1 IW 6
1 PY 7
1 RJC 8
1 PJ 9
1 GT 10
2 RJC 1
2 GWA 2
2 RJC 3
2 GWA 4
2 PY 5
2 TE 6
2 TE 7
2 RJC 8
2 PJ 9
2 GT 10
3 GT 1
3 GT 2
3 RJC 3
3 PJ 4
3 IW 5
3 TE 6
3 TE 7
3 RJC 8
3 PJ 9
3 GT 10

Thats what it looked like when i pressed submit

 

[Brianwarnock]

Will it be possible? If i group on member and race then i just need to have a custom field that then puts a position next to member that's what I'm struggling with

I didn't continue as I no longer have Access to experiment with, I've been retired 9 years, but I think that if you Groupby race and member Min on position , and also Sort on position then you will have the data you require, but I can't think off the top of my head how to do the calculated field which would enable you to just have the top 4.

Brian

 

[Brianwarnock]

Honestly its just pure lazyness to not format your data so that it is understandable enough for us to help you.

0

Blue, he did say in post 3 that he was having a problem with his post that it had changed from columns to a line.

Brian

 

[jdraw]

Sorry about the structure, I didn't realize you were having a posting format issue. However, what exactly is a race (as I understand it) where

a member can have more than one position in each race so one member could come 1,2,3,4

??

 

[carlton123]

its pigeon racing you can enter as many as you want similar to horse racing

 

[jdraw]

Please explain how the horse ---MagicBob -- can come in 1,2,3 and 4 in the same race.

Oh, ya---it's Magic Bob???

Here's a link that may have some relevance to Top N in a race. The original post was about horses at different tracks.

Good luck.

 

[carlton123]

The trainer or owner can have more than one horse in a race

 

[jdraw]

????
So a Member is an Owner ---is that relevant??

An Owner owns 1 or many Horses
A Horse may be owned by 1 or Many Owners
There may be 1 or many RaceTracks
A HorseRace occurs at 1 RaceTrack
A Race involves 1 or many Horses
a Horse has a specific finish position.
An Owner may have 1 or many horses in a race.
An Owner may own horses whose finish positions in a race are X, x+1 and x+2

I have created a sample data model of the things I think are relevant to your post. I have used the Horse race theme.

It seems 1 Owner could own many Horses, and 1 Horse could have Many Owners, that's the rationale for the junction table.
At any given race there will be distinct finish positions.

From the Horse's finish position at a specific race you can determine the Owner(s) of the Horse.

Good luck.

 

[carlton123]

yes its very relevant as that's what my query is trying to do, I can get the first position of each owner in each race but I want to rank the owners against each other, that's the bit i am struggling with.

 

[jdraw]

You're going to have to provide some sample data and from that sample the ranking you expect.
I find this description confusing --

I can get the first position of each owner in each race

I think this is the same (intent) as I can determine the owner of the Horse that finished Race X in Position Y

In plain English what do you mean by

I want to rank the owners against each other


Which owner owns most horses
Which owner has most winning horses
Which owner has a horse that came first most often
Which owner has the most horses who have raced at Track X

 

[carlton123]

Owner Race Position
RJC 1 1
RJC 1 2
GWA 1 3
RJC 1 4
TGR 1 5
TGR 2 1
GWA 2 2
GWA 2 3
RJC 2 4

Results required

Owner Race Min Position Owner Rank
RJC 1 1 1
GWA 1 3 2
TGR 1 5 3
TGR 2 1 1
GWA 2 2 2
RJC 2 4 3

 

[Brianwarnock]

Does this help

http://allenbrowne.com/ranking.html

Brian