Running frequency distributions on all fields within a table

[Nicole]

Hi-

I'm trying to find a simpler way to run a frequency table for all fields.
Right now I'm using the SQL code below to run a select query, but it only
works one field at a time. I have 200 fields and would like to run all 200
distributions at once. I have tried looking in the help documentation but
have not had success. Any ideas?


SELECT FIELD, Count(*) AS Frequency
FROM

WHERE ID Is Not Null
GROUP BY FIELD
ORDER BY FIELD;


Thanks,
Nicole

 

[Douglas J. Steele]

I can't think of any way that you'd be able to do that.

Of course, having 200 fields in a single table is extremely unusual: more
than 25 is an unusually large table!

 

[John Spencer]

The only way I know to do this is to use VBA to loop through the fields on
the table and put the data into a work table. You can paste the following
code into a VBA module, save it, and call the code to populate the table. .
You will have to build the table.

Public Sub sFrequencyCount(strTableName As String)
'CODE is not tested, but the syntax should be correct.
Dim dbAny As DAO.Database
Dim strSQL As String
Dim i As Integer
Dim strFieldName As String
'Requires a table named tblFreqCount with fields
' TableName (text -64)
' FieldName (text -64)
' FieldValue (text - 255)
' FieldCount (Number - Long)

Set dbAny = CurrentDb()

'Optional line to delete all records in tblFreqCount
dbAny.Execute "DELETE FROM tblFreqCount"

'Optional line to delete just records for specified table
dbAny.Execute "DELETE FROM tblFreqCount" & _
"WHERE tableName =""" & strTableName & """", dbFailOnError

For i = 0 To dbAny.TableDefs(strTableName).Fields.Count - 1

strFieldName = _
dbAny.TableDefs(strTableName).Fields(i).Name

strSQL = "Insert Into tblFreqCount " & _
"(TableName, FieldName, FieldValue, FieldCount)" & _
" SELECT """ & strTableName & _
""", """ & strFieldName & """" & _
", [" & strFieldName & "]" & _
", Count([" & strFieldName & "]) as theCount" & _
" FROM [" & strTableName & "] " & _
" GROUP BY [" & strFieldName & " ]"

dbAny.Execute strSQL, dbFailOnError
Next i

End Sub



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Nicole]

Thanks for the help, I really do appreciate it! Unfortunately I am
relatively new to Access and have no background in VBA. I was able to follow
what you said about creating a table with the specified fields and pasting
the code in the module. What I can't seem to find is how to call the code to
populate the table. I' ve searched in the help documentation but see
nothing.

The only way I know to do this is to use VBA to loop through the fields on
the table and put the data into a work table. You can paste the following
code into a VBA module, save it, and call the code to populate the table. .
You will have to build the table.

Public Sub sFrequencyCount(strTableName As String)
'CODE is not tested, but the syntax should be correct.
Dim dbAny As DAO.Database
Dim strSQL As String
Dim i As Integer
Dim strFieldName As String
'Requires a table named tblFreqCount with fields
' TableName (text -64)
' FieldName (text -64)
' FieldValue (text - 255)
' FieldCount (Number - Long)

Set dbAny = CurrentDb()

'Optional line to delete all records in tblFreqCount
dbAny.Execute "DELETE FROM tblFreqCount"

'Optional line to delete just records for specified table
dbAny.Execute "DELETE FROM tblFreqCount" & _
"WHERE tableName =""" & strTableName & """", dbFailOnError

For i = 0 To dbAny.TableDefs(strTableName).Fields.Count - 1

strFieldName = _
dbAny.TableDefs(strTableName).Fields(i).Name

strSQL = "Insert Into tblFreqCount " & _
"(TableName, FieldName, FieldValue, FieldCount)" & _
" SELECT """ & strTableName & _
""", """ & strFieldName & """" & _
", [" & strFieldName & "]" & _
", Count([" & strFieldName & "]) as theCount" & _
" FROM [" & strTableName & "] " & _
" GROUP BY [" & strFieldName & " ]"

dbAny.Execute strSQL, dbFailOnError
Next i

End Sub



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi-

I'm trying to find a simpler way to run a frequency table for all fields.
Right now I'm using the SQL code below to run a select query, but it only
works one field at a time. I have 200 fields and would like to run all
200
distributions at once. I have tried looking in the help documentation but
have not had success. Any ideas?


SELECT FIELD, Count(*) AS Frequency
FROM

WHERE ID Is Not Null
GROUP BY FIELD
ORDER BY FIELD;


Thanks,
Nicole

 

[John Spencer]

One way would be to use the immmediate window in VBA and just type
sFrequencyCount "Name of Your Table"

Another way would be to build a little form with a text box that allowed you
to enter the name of the table and a button with code in the click event
that read soemthing like

Private Sub btnGetFrequency_Click()
sFrequencyCount Me.txtNameOfTableNameControl
End Sub

As long as you type a valid table name in the control, this should run for
you. Then you can build a query against the table to display whatever
results you want. One thing I didn't do was to screen out field types that
would cause errors or check for valid table names in the code.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks for the help, I really do appreciate it! Unfortunately I am
relatively new to Access and have no background in VBA. I was able to
follow
what you said about creating a table with the specified fields and pasting
the code in the module. What I can't seem to find is how to call the code
to
populate the table. I' ve searched in the help documentation but see
nothing.

The only way I know to do this is to use VBA to loop through the fields
on
the table and put the data into a work table. You can paste the
following
code into a VBA module, save it, and call the code to populate the table.
.
You will have to build the table.

Public Sub sFrequencyCount(strTableName As String)
'CODE is not tested, but the syntax should be correct.
Dim dbAny As DAO.Database
Dim strSQL As String
Dim i As Integer
Dim strFieldName As String
'Requires a table named tblFreqCount with fields
' TableName (text -64)
' FieldName (text -64)
' FieldValue (text - 255)
' FieldCount (Number - Long)

Set dbAny = CurrentDb()

'Optional line to delete all records in tblFreqCount
dbAny.Execute "DELETE FROM tblFreqCount"

'Optional line to delete just records for specified table
dbAny.Execute "DELETE FROM tblFreqCount" & _
"WHERE tableName =""" & strTableName & """", dbFailOnError

For i = 0 To dbAny.TableDefs(strTableName).Fields.Count - 1

strFieldName = _
dbAny.TableDefs(strTableName).Fields(i).Name

strSQL = "Insert Into tblFreqCount " & _
"(TableName, FieldName, FieldValue, FieldCount)" & _
" SELECT """ & strTableName & _
""", """ & strFieldName & """" & _
", [" & strFieldName & "]" & _
", Count([" & strFieldName & "]) as theCount" & _
" FROM [" & strTableName & "] " & _
" GROUP BY [" & strFieldName & " ]"

dbAny.Execute strSQL, dbFailOnError
Next i

End Sub



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi-

I'm trying to find a simpler way to run a frequency table for all
fields.
Right now I'm using the SQL code below to run a select query, but it
only
works one field at a time. I have 200 fields and would like to run all
200
distributions at once. I have tried looking in the help documentation
but
have not had success. Any ideas?


SELECT FIELD, Count(*) AS Frequency
FROM

WHERE ID Is Not Null
GROUP BY FIELD
ORDER BY FIELD;


Thanks,
Nicole

 

[Nicole]

Thank you so much--that is exactly what I was looking for! One more
question... Is there a way I can force the table to count a blank value as
valid? To verify my data I need to be able to get back to the original total
record count. The fields that are entirely blank show no value and a count
of 0. Again, thanks so much for all of your help!

Nicole

One way would be to use the immmediate window in VBA and just type
sFrequencyCount "Name of Your Table"

Another way would be to build a little form with a text box that allowed you
to enter the name of the table and a button with code in the click event
that read soemthing like

Private Sub btnGetFrequency_Click()
sFrequencyCount Me.txtNameOfTableNameControl
End Sub

As long as you type a valid table name in the control, this should run for
you. Then you can build a query against the table to display whatever
results you want. One thing I didn't do was to screen out field types that
would cause errors or check for valid table names in the code.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Thanks for the help, I really do appreciate it! Unfortunately I am
relatively new to Access and have no background in VBA. I was able to
follow
what you said about creating a table with the specified fields and pasting
the code in the module. What I can't seem to find is how to call the code
to
populate the table. I' ve searched in the help documentation but see
nothing.

The only way I know to do this is to use VBA to loop through the fields
on
the table and put the data into a work table. You can paste the
following
code into a VBA module, save it, and call the code to populate the table.
.
You will have to build the table.

Public Sub sFrequencyCount(strTableName As String)
'CODE is not tested, but the syntax should be correct.
Dim dbAny As DAO.Database
Dim strSQL As String
Dim i As Integer
Dim strFieldName As String
'Requires a table named tblFreqCount with fields
' TableName (text -64)
' FieldName (text -64)
' FieldValue (text - 255)
' FieldCount (Number - Long)

Set dbAny = CurrentDb()

'Optional line to delete all records in tblFreqCount
dbAny.Execute "DELETE FROM tblFreqCount"

'Optional line to delete just records for specified table
dbAny.Execute "DELETE FROM tblFreqCount" & _
"WHERE tableName =""" & strTableName & """", dbFailOnError

For i = 0 To dbAny.TableDefs(strTableName).Fields.Count - 1

strFieldName = _
dbAny.TableDefs(strTableName).Fields(i).Name

strSQL = "Insert Into tblFreqCount " & _
"(TableName, FieldName, FieldValue, FieldCount)" & _
" SELECT """ & strTableName & _
""", """ & strFieldName & """" & _
", [" & strFieldName & "]" & _
", Count([" & strFieldName & "]) as theCount" & _
" FROM [" & strTableName & "] " & _
" GROUP BY [" & strFieldName & " ]"

dbAny.Execute strSQL, dbFailOnError
Next i

End Sub



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Hi-

I'm trying to find a simpler way to run a frequency table for all
fields.
Right now I'm using the SQL code below to run a select query, but it
only
works one field at a time. I have 200 fields and would like to run all
200
distributions at once. I have tried looking in the help documentation
but
have not had success. Any ideas?


SELECT FIELD, Count(*) AS Frequency
FROM

WHERE ID Is Not Null
GROUP BY FIELD
ORDER BY FIELD;


Thanks,
Nicole