Fishing comp database

[mellyjurgens]

I am building a database from scratch for a fishing competition. I have got 1000 registered competitors along with their photos and I can record fish weights against their ticket ID's and print leader reports of heaviest and average weights daily or overall



I now need to add a teams portion. So i have put Team IDs against the fishermen in teams and have created reports of what fish was caught by what team and their weights.

Now i need a report that retrieves the top 8 fish each day and totals them on a report to show a daily place of 1st, 2nd, 3rd. And then a report to show me the overall team winners. I MUST ONLY USE THE TOP 8 FISH WEIGHTS FROM EACH TEAM TO GET THE TOTAL but some days these teams could weigh in 32 fish. The team has a max of 4 people with max 8 fish each day each.

Can someone help me on how I can do this. I consider myself to be Beginner/Intermediate user of Access 2010.

Thanks
MellYjurgens

[NTC]

Access offers this feature embedded. I'm looking at an Access07 at the moment and layout in A10 may differ slightly. But first make a query that contains all the records of the candidates (i.e. 1 team's data maybe).

in design view, put the weight column on the left; set it to sort descending so highest wieght is on top; highlight the column; then look up in the ribbon for Query Setup area you will see a "Return" box that you can set to return the Top set. While the choices in the drop down box are 5 , 25 you can type in 8.

Note that if you have ties, that both appear - so the final return can be more than 8. but hopefully this is close enough to what you need.

Hope it helps.

[mellyjurgens]

Hi there

Thanks for the info. I do know how to do this on a single teams data but all my teams are in one table. There could be up to 50 teams, so does this mean i have to do a single query for each team?
Basically I start off with a table with all teams and weights. They fish over five days so theres alot of weights in there. I do include the day the fish was caught so i can report on daily team winners and overall.

I then need to create a query to total the teams weights, but i only want the top 8 weights of the team overall. With all the teams info in one how do you do this?

Look forward to your reply. Thanks

[NTC]

Yes I think so - 1 query / team. Of course each query is identical, except for the criteria of the team name.

But if you think about it - there is no criteria one can apply to the whole table. Each top 8 is a value unique to the team. So that this isn't a burden caused by having 1 table - it is inherent in what you seek. Having 50 separate tables wouldn't make it better - essentially it is a wash - - - and possibly worse if you want overall totals then having separate tables is a headache too.

while I can imagine a programmatic way, looping - - none of these approaches is simple. So I think working with what you have & understand is the best way to go.

[mellyjurgens]

hi there

Thanks for your advice! I'll give it a go...cheers