Query for top three and building a list from that (1 Viewer)

[Anakardian]

I have decided to try and build a database in access 2000 for keeping track of the catches we get in my fishing club.

We have yearly competition where the top 20 catches of each species give points to the overall competetion. Each member can only participate with his top three catches.

While I am able to pull a top 20 list of the complete list or for any given species, I can't figure out how I would select the top three catches of each species for each member and then create a list that shows the top 20 of each species based on its weight.
I am pretty sure there is some code that will let me do that but the whole thing keeps slipping away from me. Can anyone point me in the right direction on how to land this one?

I have constructed the database and its relationships as shown in the attached file.

 

[MStef]

Look at "DemoTopThreeA2000.mdb" (attachment, zip).
Open Form1 and see.
Adapt it in your mdb.

 

[Anakardian]

Thanks MStef, that actually look pretty simple.

I'll be looking more closely at it and see how I can adapt it for my use.
I have never been friends with access before but decided it must be something I can learn from making a fish catch database.

 

[Anakardian]

Hi again MStef

I have been studying the example you created for and think I got it more or less worked out.
From what I can see, this is what you have done.
You made a module that could count to for each member and species using the of command. When either the member of species changed, the counter would reset

You then created 2 queries.
In the first query you started by sorting the entries by member, species and weight of the catch. You then used the counting module to assign a count to each members catches. This was then saved in a new table.

In the second query you took the table that was created in the first query and limited the result to the lowest 3 values assigned by the first query. This was then saved in a new table.

The 2 queries were run by a button in a form.

If I have misunderstood something of what you have done I would appreciate if you can explain what it is.

I will take what you shown and make a new query to further limit the result of the second query to only ten of each species.
That should be a relatively simple task now you have shown the way

The next challenge will be assigning the points scored for each catch by I believe I have seen a possible solution to that posted elsewhere on the forums.

Thanks a lot for the help.

 

[MStef]

Repetitio est mater studiorum.

 

[Khalid_Afridi]

Repetitio est mater studiorum.

This is something new for me