Query to calculate percentage of field containing value` 1

[kathyrich1]

Need help with this expression: SELECT Count ([Field_Name]) / Count(*) As PercentOfField_Name From Table_Name - This expression was sent to me by a Microsoft tech support engineer, but it is not working. What I need is to calculate the percentage of records that include a value in the Field_Name field. This is to report the percentage of a project that is complete. The string does not look right to me, and I have been playing with it all day, but cannot get it right. Can someone please help???

 

[MichaelRed]

In this:

SELECT Count ([Field_Name]) / Count(*) As PercentOfField_Name From Table_Name

I would expect the "[Field_Name]" and the "Table_Name" were meant to be replaced by the specific object names from your database. It appears to be 'working' on the basis that Count([Field_Name] will count the number of records where the value of '[Field_Name]" is not null. The formula is 'correct' for theis condition, however in many instances, it will not really return the correct 'answer', as as it will count records with empty fields ("&quot as having a 'value'.

Be SURE the Field_Name & Table_Name in your 'working model' are exactly matched to the field and table names (respectively) in your table.

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over