Cricket Database

[Oswald_Cobblepot]

I have created a database for tracking the results of my U/9 team but i am having trouble getting it to retrieve best bowling (max wickets, min runs) for each player.

 

[DJN]

You can use Max and Min within your query. Check out the help.

 

[Oswald_Cobblepot]

I've tried using that but what i need is to have the stats for each separate bowler, not just the whole lot. I can get it to display what i want for 1 player by sorting wickets descending and runs ascending and then taking the top record. This only works though when i specify a player ID to look for. Is there any way to get the criteria to look at a value from a for next loop?

Thanks

 

[Oswald_Cobblepot]

Thanks for the reply.

The problem is though, that query picks the most wickets ok but when it comes to the runs it picks the lowest one in the entire field not the one that corresponds to the number of wickets found by max(wickets). The only way i have found so far is the one i mentioned previously but that will only find what i want for one player at a time. I'm not sure how to go about geting all of the players to be selected.

Thanks for the help.

 

[Oswald_Cobblepot]

Thanks a lot for that it works fine. Now i need it to pick up the Match ID from the match that it just found. I need it so it can find the date and opposition from a separate table. What can I do to retrieve it? I'm probably being a pain in the arse but thanks anyway.

 

[Oswald_Cobblepot]

When I try to join the two it comes up with the relationships that i have previously set up in the start. If the player has two separate matches with the same number of wickets, it shows them both. I don't know how to just select the first one.

Thanks
Colin

 

[Oswald_Cobblepot]

Thanks a lot, that has solved the problem perfectly. You really saved me a lot of hair pulling and keyboard smashing.

Thanks again
Colin