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
7 6073
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
ADezii 8,834
Recognized Expert Expert @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%
-
now your making it look easy- thank you
NeoPa 32,557
Recognized Expert Moderator MVP
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 8,834
Recognized Expert Expert @NeoPa Nah, not as pretty! (LOL)! -
-
Condition Percent
-
bad 0.1
-
damaged 0.2
-
good 0.1
-
new 0.4
-
old 0.2
-
NeoPa 32,557
Recognized Expert Moderator MVP
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%
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Avinash Dhoot |
last post by:
Hi,
I have a 5 row table with the following values.
key value
1 5
2 10
3 15
4 15
5 5
|
by: Daniel |
last post by:
I use an Access database to basically take data exports, import them,
manipulate the data, and then turn them into exportable reports. I do
this using numerous macros, and queries to get the data...
|
by: Mike Barnard |
last post by:
Hi all.
I am playing with html and css. I don't (yet) have a working site,
I'm just trying to build a working, basic template I can use for a
couple of ideas I have.
I recall reading a...
|
by: Bob Alston |
last post by:
I need to produce a report like this
Color: # %
------ --- ---
White 10 20
Black 25 50
other 15 30
---- ----
Total 50 100
|
by: AZKing |
last post by:
Hi all,
I would like to know how do you go about calculating percentages in Access.
For example, in a form I have 3 combo boxes with drop down menus where a user can select "Yes" or "No" and a...
| |
by: HBH |
last post by:
I am using Access 2003. I made a pie chart from a query for the sum in each catagory i am using. I want the chart to show the sum and also the percentage of the total.
|
by: eliana82 |
last post by:
I have problems calculating score percentages within groups.
I have created a boat program in access where the information provided is name, team, boat and score.
The first query I've done is...
|
by: marcnz |
last post by:
I have been charged of creating a coldfusion web site for our company.
Our database has a ms sql 2005 backend and ms access frontend. Almost all tables are linked tables with the SQL database,...
|
by: martin DH |
last post by:
Access 2003
I have a table (TASKS) filled with data from an external source. The table lists several tasks for employees to complete and has a text field (STATUS) that identifies where in the...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |