Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    bangemd is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    May 2009
    Posts
    18

    Percentage Queries

    Hello Everyone I'm new to Access



    I want to create a Queries that generate percentage for example

    I have a table that have many companies and each company is assign a fruit

    com fruit
    001 apple
    002 orange
    003 apple
    004 apple
    005 apple

    how can I generate a query that first column will list the type of fruit and the second will list the percentage compare to the sum of all the fruit. for example:

    Fruit..... percentage
    apple...... 80%
    Orange... 20%

    Thanks
    Last edited by bangemd; 05-05-2009 at 01:23 PM.

  2. #2
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Location
    Custer, WA, USA
    Posts
    125
    You could do this using a calculated field in a query, using a subquery to determine how many records there are in total.

    Something like
    Code:
    SELECT TableNameHere.Smiley AS Fruit, (100*Count([Smiley])/(SELECT Count(TableNameHere.Smiley) AS CountOfSmiley FROM TableNameHere)) AS Percentage
    FROM TableNameHere
    GROUP BY TableNameHere.Smiley;

  3. #3
    bangemd is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    May 2009
    Posts
    18
    hi Thank you for your help, I really am appreciated it. However I was unable to get it to work can you be more specific. by the way the field name is fruit not smiley, I was thinking of using smiley as example but forums only allow up to 5 smiley so it is more like this.


    I have a table that have many companies and each company is assign a fruit

    com fruit
    001 apple
    002 orange
    003 apple
    004 apple
    005 apple

    how can I generate a query that first column will list the type of fruit and the second will list the percentage compare to the sum of all the fruit. for example:

    Fruit..... %of fruit
    apple...... 80%
    Orange... 20%

    I know I am a pain in the rear end but I keep running into error when copy and paste your line into access can you be more specific as of what do I put for:

    Field:
    Table:
    Total:
    Sort:
    Show:
    Criteria:
    or:

    Thanks

  4. #4
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Location
    Custer, WA, USA
    Posts
    125
    In the example I posted, smiley was the name of the field containing the fruit text as per your original example.

    Since you have never given me the name of the table, you will have to replace all references to TableNameHere with the actual name of the table.

    If you have called the field Fruit then the SQL would look like:

    SELECT TableNameHere.Fruit, (100*Count([Fruit])/(SELECT Count(TableNameHere.Fruit) AS CountOfFruit FROM TableNameHere)) AS Percentage_of_Fruit
    FROM TableNameHere
    GROUP BY TableNameHere.Fruit;

    I tested the original SQL using a table called 'TableNameHere' and fields called com and 'smiley' with the exact data you showed in the original post and it worked just fine for me.

  5. #5
    bangemd is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    May 2009
    Posts
    18
    oh I see, so you just open up sql and edit in there, instead of do it in design view?

  6. #6
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Location
    Custer, WA, USA
    Posts
    125
    Yep Makes life easy

  7. #7
    bangemd is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    May 2009
    Posts
    18
    hi Craigdolphin, Thanks for the incredible the quick and helpful replied,

    I was able to run it smoothly after editing my sql according to what you point out. However there is a minor problem:

    This is the result after editing the SQL
    Fruit..... %of fruit
    apple...... 20%
    Orange... 20%
    apple...... 20%
    apple...... 20%
    apple...... 20%

    this is the form that I would like to display
    Fruit..... %of fruit
    apple...... 80%
    Orange... 20%

    thanks for taking your time to help

  8. #8
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Location
    Custer, WA, USA
    Posts
    125
    Did you make sure to include the group by portion of the SQL?

  9. #9
    bangemd is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    May 2009
    Posts
    18
    hehe Thanks a million CraigDolphin, I was grouping them using the Companies instead of the fruit. It all work now I learned so much.

  10. #10
    CraigDolphin is offline Competent Performer
    Windows XP Access 2000
    Join Date
    Apr 2009
    Location
    Custer, WA, USA
    Posts
    125
    Excellent

  11. #11
    bangemd is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    May 2009
    Posts
    18
    I have a new question since we got that one out the way,

    If I have another field
    com fruit..... Status
    001 apple.... good
    002 orange.. good
    003 apple.... Bad
    004 apple.... Bad
    005 apple.... Good

    and I want the query to display the % of status field compare to the number of company that have a particular fruit:
    the calculation is something like this
    fruit.... total.. good. Bad.. %of good.. % of bad
    Orange. 1...... 1..... 0...... . 100%..... ....o%
    apple... 4....... 2..... 2..... .. 50%...... ...50%

    The new query will look something like this
    Fruit..... %of fruit %of good %of bad
    apple...... 80%..... ...100%. ...0%
    Orange... 20%...... ...50%.. ...50%..

  12. #12
    thhui is offline Competent Performer
    Windows XP Access 2002 (version 10.0)
    Join Date
    Feb 2009
    Posts
    235
    The code for the new question is as follows:-
    I use iif function to count the good and the bad

    Code:
    SELECT fruit, 
    Count([fruit])/(Select Count([fruit]) from Table1) AS [% of Fruit], 
    Sum(iif([Status]="Good",1,0))/Count([Status]) AS [% of Good], 
    Sum(iif([Status]="Bad",1,0))/Count([Status]) AS [% of Bad] 
    FROM Table1 
    GROUP BY fruit;
    Last edited by thhui; 05-13-2009 at 06:33 AM.

  13. #13
    bangemd is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    May 2009
    Posts
    18
    thanks thhui I'll try it and let you know

  14. #14
    bangemd is offline Novice
    Windows XP Access 2002 (version 10.0)
    Join Date
    May 2009
    Posts
    18
    I copy and paste it just as you type it for some reason it keep giving me the error "the select statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect." and it high light the first sum in the sql. I check all the punctuation and it seem correct.

    again thank you for helping

  15. #15
    thhui is offline Competent Performer
    Windows XP Access 2002 (version 10.0)
    Join Date
    Feb 2009
    Posts
    235
    I have tried the code in my database and table and it works!

    Pls check your code for the table name as mine is Table1.
    Others are the same as yours.
    Last edited by thhui; 05-13-2009 at 06:34 AM.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. need help with queries
    By gromit95 in forum Queries
    Replies: 1
    Last Post: 02-06-2009, 06:50 AM
  2. percentage in a query
    By Peljo in forum Access
    Replies: 2
    Last Post: 02-27-2008, 10:51 AM
  3. Crosstab Queries
    By albst130 in forum Queries
    Replies: 0
    Last Post: 03-07-2007, 09:32 AM
  4. Queries the max from two rows
    By mohsin74 in forum Queries
    Replies: 0
    Last Post: 12-15-2006, 12:52 AM
  5. VBA and stacked queries
    By Pudy in forum Queries
    Replies: 0
    Last Post: 11-28-2006, 03:58 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums