Thread: Query...How To Percentages?

1. Registered User
Join Date
Aug 2004
Posts
40

Unanswered: Query...How To Percentages?

I have started recording the scores for every three dart throw (total) by six different dart players. I have a simple query that returns two fields, they are "Name" & "ThreeDartScore". When I run my query it returns 221 records with multiple entries for each of the six players. For example:
Fred - 80
Fred - 120
Fred - 45
Bob - 100
Bob - 95
Bob - 140
What I would like to do is create another query based on these results that returns a percentage for each individual player. The percentage would be based on how many times each player's ThreeDartScore equals or exceeds 100 compared to his total number of records. For example, the ideal query result would be:
Fred - 33%
Bob - 66%
I can do this manualy by sorting and reporting but naturally I would prefer to have a query do it for me automatically instead. If anyone can assist I would greatly appreciate it.
Many thanks in advance.
Ronald A. Dixon

2. Registered User
Join Date
Feb 2004
Location
Chicago, IL
Posts
1,312
All you will need to do is add a column to your query:

Over100:IIf([Score]>99,1,0)

Your results will look like:
Fred - 80 - 0
Fred - 120 - 1
Fred - 45 - 0
Bob - 100 - 1
Bob - 95 - 0
Bob - 140 - 1

Then Group By the player, count the number of scores and sum the number of Over100 scores.

The result should be something like:
Fred - 3 - 1
Bob - 3 - 2

THen you can have a second query that calculates the percentage of Over100 divided by the COunt of the scores.

3. Registered User
Join Date
Sep 2004
Location
Charlotte, NC
Posts
164
This only requires a single query.
SELECT Scoring.Name, Sum(IIf([threedartscore]>99,1,0))/Count([threedartscore]) AS Percentage
FROM Scoring
GROUP BY Scoring.Name;

TD

4. Registered User
Join Date
Aug 2004
Posts
40
Thank You...I have the new column working fine (0s and 1s) but I don't know how to do the rest of it i.e "Then Group By the player, count the number of scores and sum the number of Over100 scores" can you help a little further please?
Many Thanks
Ronald A. Dixon

5. Registered User
Join Date
Aug 2004
Posts
40
TD...thank you! but I am a novice. Can I copy and paste your instructions somewhere within the query I already have, or do I need to make a new query?
Many thanks.
Ronald A. Dixon

6. Registered User
Join Date
Sep 2004
Location
Charlotte, NC
Posts
164
Ronald,

In a select query, place your field called [Name] in the first column and right-click on the field and select 'Totals' from the floating menu and then make sure 'Group By' shows in the Total row for the [Name] field. Go to the next column over and in the Field cell, paste this without the quotes:
"Percentage: Sum(IIf([threedartscore]>99,1,0))/Count([threedartscore])"
and then select 'Expression' in the Total row.

TD

7. Registered User
Join Date
Aug 2004
Posts
40
TD...many thanks again but I still haven't got it right. Now, the query result is a NEW field called "Percentage" and the data returned in it is 0 or 1. I hate to be a pest but can you help a little further please?
Regards,
Ronald A. Dixon

8. Registered User
Join Date
Sep 2004
Location
Charlotte, NC
Posts
164
Try selecting your Percentage field in designview and check the field properties and set the Format to 'Percent' and see if that fixes it.

TD

9. Registered User
Join Date
Aug 2004
Posts
40
TD...thanks again and well done The data in the new "Percentage" field is now displayed in the following format: either 100.00% or 0.00% and the query is still returning 221 records when ideally I only need to return 6 records, one for each player, and each player will have varying 100+ scoring percentages. Can you assist any further please?
Regards,
Ronald A. Dixon

10. Registered User
Join Date
Sep 2004
Location
Charlotte, NC
Posts
164
How many fields are in your query and what do you have selected on the total line for each field; ie: Group By, Count, Expression, etc.? Would it be possible for you to zip the database and attach it to your reply so I can look at the query structure?

11. Registered User
Join Date
Aug 2004
Posts
40
TD...wait for it...the query contains 15 fields in total, 13 of them say Group By and the other 2 say Expression. The expression fields are the new "Percentage" field and the other field is a Date Diff calculating field. I only used the Dart Player scenario to avoid complicated explanation of my many fields. My database is currently 299mb and growing so a zip file is out of the question I'm afraid. If I have to use two or more queries to get the desired result I don't mind that at all. Thanks again for your kind help, it is much appreciated.
Regards,
Ronald A. Dixon

12. Registered User
Join Date
Aug 2004
Posts
40

Query...How To Percentage?

TD...I have zipped and attached a small sample database as you suggested last night. Thanks to your kind help on that occasion this is very close to what I want, but I would like the query to return only 3 records (one for each player) which in this case if I had it working properly it would show Bob - 33.3% Fred - 66% Harry - 100% The idea is to show a percentage "strike rate" for scores of 100 or more based on a comparison with each players total number or throws. If TD is not available this evening then I would be happy for anyone else to pick up this thred and try to shed some light on it for me. Many thanks to all in advance.
Kind Regards,
Ronald A. Dixon

13. Registered User
Join Date
Sep 2004
Location
Charlotte, NC
Posts
164
Remove the ThreeDartScore column from your query. Also modify the Percentage calculation to say >99 since you want 100 to count.

TD
Last edited by buckeye_td; 11-11-04 at 16:57.

14. Registered User
Join Date
Aug 2004
Posts
40
TD...Congratulations and many thanks! Your kind help has been very much appreciated. I wish you well.
Kind Regards,
Ronald A. Dixon

15. Registered User
Join Date
Aug 2004
Posts
40

Query...How To Show Only Latest Date?

I have attached a small sample database. It contains a simple query "qryHighestScore" that contains three fields "MatchDate", "PlayerName" and "ThreeDartScore" When I run this query it returns 9 records (3 for each named player) I would like to modify the query a little further or build a new query that will return only 3 records (1 for each player) I have already done something similar to this with the help of TD from this forum using the Max option but this time I want the sort to be based on the new "MatchDate" field. For example, the ideal query would return only three records, based on the sample data those records would be:
02/Jun/04 - Bob
05/Apr/03 - Fred
12/Feb/99 - Harry
Many thanks in advance,
Regards,
Ronald A. Dixon

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•