Percentage of Complete records

[Jeff via AccessMonster.com]

I have a query which generates a list of records.
Each record has about 50 fields and it is important that all these fields
are populated.
I am looking for a quick way to calculate the percentage of completion for
all records.
This will tell me at a glance how complete my recordset is.
I was thinking of adding a test field for each field of the query that is
equal to 0 or 1 depending on whether it is complete or not, then add all
these values per record.Then sum all these values and compare against the
product of number of fields per record x count of records.
This would work but is very time consuming to create and not easy to maintain
if I change the number of fields...etc

Any better idea?

Jeff

 

[Jake]

I am not clear if you are looking for the percent complete for each record or
the percentage of the records that are complete.

If you are looking for the percentage of records that are complete then here
is somehting that should work, otherwise let me know and I will address the
other situation:

o Create a query and add all 50 values that you need to test and set the
criteria for each to "not null"
o Now you can compare the number of rows in the table to the number of rows
in the query to get the percentage completed.

 

[Dorian]

Enforce the completion of the fields when the data is entered, not afterwards.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[John Spencer]

You can try the following sub. Paste it into a VBA module and save.

Public Sub fTestBlanks(strSQL As String)
' Test for blank fields
'strSQL is a valid SQL statement.
'to test for all fields being blank the string
'would look like
' SELECT * FROM [NameOfTable]

Dim dbAny As DAO.Database
Dim rstAny As DAO.Recordset
Dim iLoop As Long
Dim iFldCount As Long
Dim iRecCount As Long
Dim iBlankCount As Long
Dim iRecBlank As Long: Dim tfBlankRecord As Boolean

Set dbAny = CurrentDb
Set rstAny = dbAny.OpenRecordset(strSQL)
iFldCount = rstAny.Fields.Count

If rstAny.RecordCount < 1 Then
MsgBox "No records to check"
Else
'Loop through all the records and fields in the query
While rstAny.EOF = False
iRecCount = iRecCount + 1
tfBlankRecord = False
For iLoop = 0 To iFldCount - 1
If Len(rstAny.Fields(iLoop) & "") = 0 Then
iBlankCount = iBlankCount + 1
tfBlankRecord = True
End If
Next
If tfBlankRecord Then
iRecBlank = iRecBlank + 1
End If
rstAny.MoveNext
Wend
'Message reporting percentage of blank fields and
'percentage of records with blank fields.
MsgBox Format(iBlankCount / (iRecCount * iFldCount), "Percent") & _
" fields are blank" & _
vbCrLf & _
Format(iRecBlank / iRecCount, "Percent") & _
" records have one or more blank fields"
End If

End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[jfsauzeat129 via AccessMonster.com]

Thank you so much for the answers.
I cannot enforce the completion as the data is entered because the data is
not entered all at the same time for each record.
The query will give me insuffient information. I will try the code that was
written in the last post
thanks again for the help!

You can try the following sub. Paste it into a VBA module and save.

Public Sub fTestBlanks(strSQL As String)
' Test for blank fields
'strSQL is a valid SQL statement.
'to test for all fields being blank the string
'would look like
' SELECT * FROM [NameOfTable]

Dim dbAny As DAO.Database
Dim rstAny As DAO.Recordset
Dim iLoop As Long
Dim iFldCount As Long
Dim iRecCount As Long
Dim iBlankCount As Long
Dim iRecBlank As Long: Dim tfBlankRecord As Boolean

Set dbAny = CurrentDb
Set rstAny = dbAny.OpenRecordset(strSQL)
iFldCount = rstAny.Fields.Count

If rstAny.RecordCount < 1 Then
MsgBox "No records to check"
Else
'Loop through all the records and fields in the query
While rstAny.EOF = False
iRecCount = iRecCount + 1
tfBlankRecord = False
For iLoop = 0 To iFldCount - 1
If Len(rstAny.Fields(iLoop) & "") = 0 Then
iBlankCount = iBlankCount + 1
tfBlankRecord = True
End If
Next
If tfBlankRecord Then
iRecBlank = iRecBlank + 1
End If
rstAny.MoveNext
Wend
'Message reporting percentage of blank fields and
'percentage of records with blank fields.
MsgBox Format(iBlankCount / (iRecCount * iFldCount), "Percent") & _
" fields are blank" & _
vbCrLf & _
Format(iRecBlank / iRecCount, "Percent") & _
" records have one or more blank fields"
End If

End Sub

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

I have a query which generates a list of records.
Each record has about 50 fields and it is important that all these fields

[quoted text clipped - 12 lines]

Jeff

 

[John Spencer]

If you are looking for the amount completed per record and want to
return records that are incomplete the record then you would need to
modify that function significantly. Or perhaps not -

You could change it to return true or false and use a subquery

SELECT SomeTable.*
FROM SomeTable
WHERE fTestBlanks("SELECT * FROM SomeTable WHERE PrimaryKeyField=" &
PrimaryKeyField) = True


and the modified code would probably look like the following UNTESTED VBA

Public Function fTestBlanks(strSQL As String) as Boolean
' Test for blank fields
'strSQL is a valid SQL statement.
'to test for all fields being blank the string
'would look like
' SELECT * FROM [NameOfTable]

Dim dbAny As DAO.Database
Dim rstAny As DAO.Recordset
Dim iLoop As Long
Dim iFldCount As Long
Dim iRecCount As Long
Dim iBlankCount As Long
Dim iRecBlank As Long: Dim tfBlankRecord As Boolean

Set dbAny = CurrentDb
Set rstAny = dbAny.OpenRecordset(strSQL)
iFldCount = rstAny.Fields.Count

If rstAny.RecordCount < 1 Then
ftestBlanks = false
Else
'Loop through all the records and fields in the query
While rstAny.EOF = False
iRecCount = iRecCount + 1
tfBlankRecord = False
For iLoop = 0 To iFldCount - 1
If Len(rstAny.Fields(iLoop) & "") = 0 Then
fTestBlanks = True
exit for
End If
Next
rstAny.MoveNext
Wend
End If

End Function
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================