Find percentages in Access query

[buddyr]

Hello,
can I write a query that will give percentages of records in one field?
table example:

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

[DonRayner]

Based on your table data above you could do it like this

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

[buddyr]

thank you- that works

[ADezii]

@buddyr
Another Option (change to your Table Name)

SELECT DISTINCT ServiceRec.Condition, Format(DCount("*","ServiceRec","[Condition] = '" & 
[Condition] & "'")/DCount("*","ServiceRec"),"Percent") AS [Percent]
FROM ServiceRec
ORDER BY ServiceRec.Condition;

 
Condition            Percent
bad                   10.00%
damaged               20.00%
good                  10.00%
new                   40.00%
old                   20.00%
 

[buddyr]

now your making it look easy- thank you

[NeoPa]

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.

SELECT [Condition],
       Count(*)/DCount('*','[ServiceRec]') AS [Percent]
 
FROM [ServiceRec]
 
GROUP BY [Condition]
 
ORDER BY [Condition]

[ADezii]

@NeoPa
Nah, not as pretty! (LOL)!

 
Condition            Percent 
bad                   0.1 
damaged               0.2 
good                  0.1 
new                   0.4 
old                   0.2 
 

[NeoPa]

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 ;)

Condition            Percent 
Bad                   10%
Damaged               20%
Good                  10%
New                   40%
Old                   20%