Cricket Database (1 Viewer)

[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

 

[Pat Hartman]

Select Player, Max(Wickets), Min(Runs)
From YourTable
Group By Player;

 

[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.

 

[Pat Hartman]

Your original statement said you wanted the Max() wickets and the Min() runs. What you are saying now is different.

If you want to pick up data assocated with a record you find using one of the aggregate functions such as Max(), you need to separate the process into two queries. The first query finds the Max() and the second query uses that information to join back to the main table to pick up the associated information.

Query1:
Select Player, Max(Wickets) As MaxWickets
From YourTable
Group By Player;

Query2:
Select q.Player, q.MaxWickets, t.Runs
From query1 as q inner join YourTable as t on q.player = t.player and q.MaxWickets = t.Wickets;

 

[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.

 

[Pat Hartman]

You would modify query2 to include the matchID as a selected field from the table and include a join to the other table. To get the join right, just add the new table to the QBE grid and draw the join line between the MatchId in the original table to the MatchId in the newly added table. Then select the extra fields you need.

 

[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

 

[Pat Hartman]

Use the First() function in the final query.

By the way, I did not understand the question in your email. Perhaps you should post it here.

 

[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