Seems so simple - Percentage

D

Danu

I have a table with the code designation in one column and the number of
records with that code in the other column. I need to find out what
percentage of the total records in the table has each code. Seems so simple
but I cannot get the query to work!

Please help.

Thanks.
 
J

Jerry Whittle

SELECT ASIF.TABLE_NAME,
Count(ASIF.TABLE_NAME) AS TheCount,
Format([TheCount]/DCount("[ID]","ASIF"),"Percent") AS Expr1
FROM ASIF
GROUP BY ASIF.TABLE_NAME;

Try something like the above with the proper table and field names. ID is
the primary key field for the table. Using a PK field makes sure that nulls
won't mess up things.
 
K

Ken Sheridan

You don't need to store the number of rows per code in a column; you can
compute that in a query and at the same time get the percentage of the total
number of rows, e.g.

SELECT Code, COUNT(*) As NumberPerCode,
100*COUNT(*)/
(SELECT COUNT(*)
FROM YourTable)
AS Percentage
FROM YourTable
GROUP BY Code;

Ken Sheridan
Stafford, England
 
D

Danu

Thank you!

Ken Sheridan said:
You don't need to store the number of rows per code in a column; you can
compute that in a query and at the same time get the percentage of the total
number of rows, e.g.

SELECT Code, COUNT(*) As NumberPerCode,
100*COUNT(*)/
(SELECT COUNT(*)
FROM YourTable)
AS Percentage
FROM YourTable
GROUP BY Code;

Ken Sheridan
Stafford, England
Click to expand...
 
D

Danu

Thank you!

Jerry Whittle said:
SELECT ASIF.TABLE_NAME,
Count(ASIF.TABLE_NAME) AS TheCount,
Format([TheCount]/DCount("[ID]","ASIF"),"Percent") AS Expr1
FROM ASIF
GROUP BY ASIF.TABLE_NAME;

Try something like the above with the proper table and field names. ID is
the primary key field for the table. Using a PK field makes sure that nulls
won't mess up things.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Danu said:
I have a table with the code designation in one column and the number of
records with that code in the other column. I need to find out what
percentage of the total records in the table has each code. Seems so simple
but I cannot get the query to work!

Please help.

Thanks.
Click to expand...
Click to expand...
 
Top