By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,486 Members | 2,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,486 IT Pros & Developers. It's quick & easy.

Find percentages in Access query

100+
P: 107
Hello,
can I write a query that will give percentages of records in one field?
table example:
Expand|Select|Wrap|Line Numbers
  1. table1
  2. IdNumber    model    condition
  3. 01              045         good
  4. 01              045         bad
  5. 02              046         new
  6. 03              047         damaged
  7. 02              046         new
  8. 02              046         old
  9. 03              047         new
  10. 03              047         new
  11. 01              045         damaged
I have 4 things the field conditon can be good, bad, new, and damaged.
Is there any way to show them in percent values?
thank you
Jan 22 '09 #1
Share this Question
Share on Google+
7 Replies


DonRayner
Expert 100+
P: 489
Based on your table data above you could do it like this

Expand|Select|Wrap|Line Numbers
  1. SELECT ((Sum(IIf([Table1]![condition]="new",1,0)))*100)/Sum(IIf([Table1]![condition] Like "*",1,0)) AS NewCount, ((Sum(IIf([Table1]![condition]="old",1,0)))*100)/Sum(IIf([Table1]![condition] Like "*",1,0)) AS OldCount, ((Sum(IIf([Table1]![condition]="good",1,0)))*100)/Sum(IIf([Table1]![condition] Like "*",1,0)) AS GoodCount, ((Sum(IIf([Table1]![condition]="bad",1,0)))*100)/Sum(IIf([Table1]![condition] Like "*",1,0)) AS BadCount, ((Sum(IIf([Table1]![condition]="damaged",1,0)))*100)/Sum(IIf([Table1]![condition] Like "*",1,0)) AS DamagedCount
  2. FROM Table1;
  3.  
Just change all the "Table1" to whatever you table name is
Jan 22 '09 #2

100+
P: 107
thank you- that works
Jan 23 '09 #3

ADezii
Expert 5K+
P: 8,597
@buddyr
Another Option (change to your Table Name)
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT ServiceRec.Condition, Format(DCount("*","ServiceRec","[Condition] = '" & 
  2. [Condition] & "'")/DCount("*","ServiceRec"),"Percent") AS [Percent]
  3. FROM ServiceRec
  4. ORDER BY ServiceRec.Condition;
Expand|Select|Wrap|Line Numbers
  1.  
  2. Condition            Percent
  3. bad                   10.00%
  4. damaged               20.00%
  5. good                  10.00%
  6. new                   40.00%
  7. old                   20.00%
  8.  
Jan 23 '09 #4

100+
P: 107
now your making it look easy- thank you
Jan 23 '09 #5

NeoPa
Expert Mod 15k+
P: 31,186
To reduce the number of function calls, I've started with ADezii's SQL and doctored it a little. I've also allowed the formatting to be controlled by whatever is using the query. Format() returns a string which may not always suit your requirements.
Expand|Select|Wrap|Line Numbers
  1. SELECT [Condition],
  2.        Count(*)/DCount('*','[ServiceRec]') AS [Percent]
  3.  
  4. FROM [ServiceRec]
  5.  
  6. GROUP BY [Condition]
  7.  
  8. ORDER BY [Condition]
Jan 23 '09 #6

ADezii
Expert 5K+
P: 8,597
@NeoPa
Nah, not as pretty! (LOL)!
Expand|Select|Wrap|Line Numbers
  1.  
  2. Condition            Percent 
  3. bad                   0.1 
  4. damaged               0.2 
  5. good                  0.1 
  6. new                   0.4 
  7. old                   0.2 
  8.  
Jan 24 '09 #7

NeoPa
Expert Mod 15k+
P: 31,186
That's cos you missed a bit A - XD.
The formatting is done in whatever uses the SQL (QueryDef, Report, etc).
That way it shows how you want it when displayed, and is also usable as a value in the code ;)
Expand|Select|Wrap|Line Numbers
  1. Condition            Percent 
  2. Bad                   10%
  3. Damaged               20%
  4. Good                  10%
  5. New                   40%
  6. Old                   20%
Jan 24 '09 #8

Post your reply

Sign in to post your reply or Sign up for a free account.