If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Seems so simple - Percentage
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. |
#2
|
|||
|
|||
Seems so simple - Percentage
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" wrote: 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. |
#3
|
|||
|
|||
Seems so simple - Percentage
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 "Danu" wrote: 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. |
#4
|
|||
|
|||
Seems so simple - Percentage
Thank you!
"Ken Sheridan" wrote: 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 "Danu" wrote: 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. |
#5
|
|||
|
|||
Seems so simple - Percentage
Thank you!
"Jerry Whittle" wrote: 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" wrote: 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. |
Thread Tools | |
Display Modes | |
|
|