434,894 Members | 1,942 Online
Need help? Post your question and get tips & solutions from a community of 434,894 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 table1 IdNumber    model    condition 01              045         good 01              045         bad 02              046         new 03              047         damaged 02              046         new 02              046         old 03              047         new 03              047         new 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
7 Replies

 Expert 100+ P: 489 Based on your table data above you could do it like this Expand|Select|Wrap|Line Numbers 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 FROM Table1;   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

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

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

 Expert Mod 15k+ P: 31,491 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 SELECT [Condition],        Count(*)/DCount('*','[ServiceRec]') AS [Percent]   FROM [ServiceRec]   GROUP BY [Condition]   ORDER BY [Condition] Jan 23 '09 #6

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

 Expert Mod 15k+ P: 31,491 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 Condition            Percent  Bad                   10% Damaged               20% Good                  10% New                   40% Old                   20% Jan 24 '09 #8