Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    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. #2
    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. #3
    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. #4
    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. #5
    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. #6
    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. #7
    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. #8
    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. #9
    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. #10
    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. #11
    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. #12
    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
    Attached Files Attached Files

  13. #13
    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. #14
    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. #15
    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
    Attached Files Attached Files

Posting Permissions

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