Seems so simple - Percentage

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

Thank you!

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

 

[Guest]

Thank you!

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.

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.