Work out %s from grouped records

Jossy · Aug 2, 2019

Hi. I have a database of tennis matches for which I've built a query that gives me columns for the player's name, the number of times a first serve was won and the number of first serves that were 'called in'. As the query originally pulled all the matches and thus duplicated each player's name I added column totals and grouped by player name by selecting sum for fields 2 and 3.

Now I want to work out the % of times a first serve that was called in was won by dividing column 2 by column 3. I duly added a new expression field doing just that but as this wasn't included in the grouping I got all the duplicate player names back again.

I'm now stuck as to how to get the true % as sum seems to run the calculation for each instance of the player name and then sum these figures - this unsurprisingly gives over 100% in some cases. What should I do? Thanks in advance.

arnelgp · Aug 2, 2019

if you can post the name of the table and the names of the columns involved in the query perhaps we can build the query.
anyway, I am thinking something like this is want you are desiring:

Code:

SELECT DISTINCT [playerNameColumn], (SELECT COUNT("1") 
FROM table1 AS T1 WHERE T1.[firstServeColumnWon]=True 
AND T1.[playerNameColumn]=table1.[playerNameColumn]) AS FirstServeWon, 
(SELECT COUNT("1") 
FROM table1 AS T1 WHERE T1.[firstServeIn]=True 
AND T1.[playerNameColumn]=table1.[playerNameColumn]) AS FirstServeIn,
(SELECT COUNT("1") 
FROM table1 AS T1 WHERE T1.[firstServeColumnWon]=True 
AND T1.[playerNameColumn]=table1.[playerNameColumn]) / 
(SELECT COUNT("1") 
FROM table1 AS T1 WHERE T1.[firstServeIn]=True 
AND T1.[playerNameColumn]=table1.[playerNameColumn]) * 100 
AS Percentage 
FROM table1;

Jossy · Aug 2, 2019

Hi. Thanks for the reply. Thought I'd post the SQL that might be the clearest thing. Worth mentioning I'm still at a basic stage with Access and SQL (started reading Access for Dummies last week).

So the situation is a little more complicated than I explained in my first mail - I genuinely thought this would be a quick fix I could apply using the design view!

To explain the more detailed version... I am working from a main table where there is a column for the first player and then their stats plus a column for the second player and then their stats. As the first player in one match can be the opponent in another then I have built a union query that extracts the stats for both player 1 and player 2 for every match and combines them together:

Code:

SELECT stat_atp.ID1, stat_atp.W1S_1, stat_atp.W1SOF_1
FROM stat_atp

UNION

SELECT stat_atp.ID2, stat_atp.W1S_2, stat_atp.W1SOF_2
FROM stat_atp;

As I don't know how to add grouping to the SQL (I did try!) I simply created another query to reference this query where I could add the grouping in design view and an expression field to work out the %s:

Code:

SELECT [Copy of S&R_Comb_atp].ID1, Sum([Copy of S&R_Comb_atp].W1S_1) AS SumOfW1S_1, Sum([Copy of S&R_Comb_atp].W1SOF_1) AS SumOfW1SOF_1, Sum(IIf([W1S_1]=0 Or [W1SOF_1]=0,0,[W1S_1]/[W1SOF_1])) AS W1Spct
FROM [Copy of S&R_Comb_atp]
GROUP BY [Copy of S&R_Comb_atp].ID1;

Does this help at all? Is there anyway to do what I want using the design view or will I have to learn SQL?

Jossy · Aug 9, 2019

Not sure if anyone was able to help with this?

Work out %s from grouped records

Jossy

Registered User.

arnelgp

..forever waiting... waiting for jellybean!

Jossy

Registered User.

Jossy

Registered User.

Similar threads

Users who are viewing this thread