SQL code for frequencies of all variables in table

[janelange]

Hi,

I have a table with a large number of columns(>100) and want to use sql to calculate the frequency of particular responses for each column in the list (where these frequencies do not include the missing data in the category).

I know this shouldn't be too difficult, but would prefer to write a sql statement that does not list all the column names individually or specify the responses to be counted for each of the variables.

I do have another table with all of the column names.

Any thoughts?

Thanks,
Jane L.

 

[Remou]

Does it have to be SQL, or would an adaptation of something like this work(?):

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select * From Statistics")
For Each fld In rs.Fields
    If IsNumeric(fld) Then
        v = DCount(fld.Name & ">0", "Statistics")
    Else
        v = DCount(fld.Name, "Statistics")
    End If
Next
rs.Close
Set rs = Nothing

 

[janelange]

That definitely looks like it would work, but I'm trying to actually write this code to use with SAS (which I'm using because my table has too many variables to fit into Access), and am more comfortable with SQL than with SAS syntax, which seems to lack an easy way of exporting the information on frequency of all the variables in a dataset.

Perhaps I should ask the folks over in the SAS forum, unless you know of a way to externally link to sas datasets as DAO objects.

Jane

 

[BoxHead]

Jane,

I'm not sure if this is helpful or not, but to restructure the data in a wide table, the following code on a command button takes 130 columns of data and transposes it into a three-column table with the field name, each value in that field, and the count of each value.

My sample table had field names like fNm001-fNm130 and I plugged in the days of the week randomly for values.
tblWeekdays

fNm001 fNm002 fNm003 ..... fNm130
Sunday Tuesday Thursday ..... Sunday
Monday Tuesday Wednesday ..... Monday
Friday Wednesday Friday ..... Friday


I built the 3-field table before beginning and the code includes a Delete query to clear the data out for updating.

tblS
myFldName myFldValue myValueCount
fNm001 Sunday 38
fNm001 Thursday 23
fNm001 Tuesday 17
fNm001 Wednesday 36
fNm002 Friday 30
fNm002 Monday 29
fNm002 Saturday 45
fNm002 Sunday 10


I built the 3-field table before beginning and the code includes a Delete query to clear the data out for updating.

Private Sub c1_Click()
Dim db1 As DAO.Database, rs1 As DAO.Recordset
Dim intI As Integer, sql1 As String
Set db1 = CurrentDb
Set rs1 = db1.OpenRecordset("tblWeekdays", dbOpenDynaset)

DoCmd.SetWarnings False

DoCmd.RunSQL "DELETE * FROM tblS;"  [COLOR=green]'clear out existing records[/color]

For intI = 1 To rs1.Fields.Count - 1
sql1 = "INSERT INTO tblS ( myFldName, myFldValue, myValueCount ) SELECT " & """" & rs1.Fields(intI).Name & """" & ", " _
& "[" & rs1.Fields(intI).Name & "]" & ", Count(" & rs1.Fields(0) & ") FROM tblWeekdays GROUP BY " & rs1.Fields(intI).Name & ", " & "[" & rs1.Fields(intI).Name & "]" & ";"
DoCmd.RunSQL sql1
Next intI

DoCmd.SetWarnings True

End Sub

Like I said, I don't know if it will be helpful to you but I've seen a number of posts similar to yours and decided I wanted to figure something out.







John

Use what you have,
Learn what you can,
Create what you need.

 

[Remou]

Dim rs As DAO.Recordset
Dim strSQL As String
Dim qdf As QueryDef
Dim strQueryName As String
Dim i, tn

'Build string
strSQL = "SELECT "
Set rs = CurrentDb.OpenRecordset("Select * From " & tblName)
For Each fld In rs.Fields
    If IsNumeric(fld) Then
        'If zeros should not be counted, show them as null
        strSQL = strSQL & "Count(IIf([" & fld.Name _
          & "]=0,Null,[" & fld.Name & "])) AS Count" & fld.Name & ", "
    Else
        strSQL = strSQL & "Count(" & fld.Name _
          & ") AS Count" & fld.Name & ", "
    End If
Next
rs.Close
Set rs = Nothing

strSQL = Left(strSQL, Len(strSQL) - 2) & " FROM " & tblName

'Create query
strQueryName = "qryCount" & tblName
i = 1
tn = strQueryName
Do While QueryExists(strQueryName)
    strQueryName = tn & i
    i = i + 1
Loop

Set qdf = CurrentDb.CreateQueryDef(strQueryName, strSQL)
'Open query
'DoCmd.OpenQuery qdf.Name
'Or export to text
'DoCmd.TransferText acExportDelim, , qdf.Name, qdf.Name & ".txt"
End Sub

Function QueryExists(strQueryName As String) As Boolean
    ' This procedure returns True or False depending on whether
    ' the table named in strQueryName exists.
    Dim dbs As Database, qdf As QueryDef

    On Error Resume Next
    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs(strQueryName)
    If Err = 3265 Then
        ' Query does not exist.
        QueryExists = False
    Else
        ' Query exists.
        QueryExists = True
    End If
    Err = 0
End Function

 

[janelange]

Thanks for the suggestions!

Do you know if there's any way to adjust the maxium number of fields in a table in Access? (which would allow me to use your code)? Currently the dataset I'm working with has 1300+ fields (it's from a very long questionnaire) and there's no way to actually get the table into access, allowing for use of this nice VB code? The microsoft specifications say that 255 is the max number of fields in a table, but is this adjustable?

Thanks
Jane

 

[Golom]

No it isn't adjustable. That's a hard limit. It does sound like you are "commiting spreadsheet" in your database design. You usually build this sort of thing with tables of the form
[tt]
tblQuestions
QuestionNumber Response
1 Yes
2 No
3 Maybe
: :
1200 Only on Wednesdays
1201 Sometimes
[/tt]
Then your SQL gets simple

Select Response, Count(Response) As Frequency
From tblQuestions
Group By Response

 

[janelange]

That certainly makes sense, generally, although I inherited this dataset from someone else--and for data manipulation purposes, (summary scores and the like), it is more manageable to treat each of the answers as separate variables.

Thanks for your help.

 

[lespaul]

it is more manageable to treat each of the answers as separate variables.

If it is more manageable to have it in the format your are using, why do you think it is so difficult for you to get the information you need?

It's because it's committing spreadsheet as pointed out above. A relational database is NOT more manageable the way you have it structured. Here's a link to a some sample databases, if you look for Duane Hookom's area, there's a sample survey database.

As Golom pointed out, the SQL for frequency is extremely simple if the database table is structured in a relational manner as opposed to a spreadsheet manner. That's the power of the model.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[janelange]

Thanks for pointing out the articles--I'll definitely have a read.

I guess my point was that for data manipulation purposes in SAS (which is where I'm doing most of the data management), in which individual variables are used to create derived summary variables--a spreadsheet style structure has utility. But agreed, for purposes like this one it's a cumbersome way to structure the data...

Best,
Jane

 

[BoxHead]

Jane,

I have no familiarity with SAS but if you can link Access to that table, a variation of the code I provided may work to get the data into Access in a more normalized fashion. It looks at the columns of the table one at a time so it may be that the 255-column limit is never an issue.

As I say, I don't know for sure but you may want to look into it some more.



John

Use what you have,
Learn what you can,
Create what you need.

 

[dhookom]

Come on back if you have any questions about my "At Your Survey". I prefer answering in news groups and forums.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[janelange]

I just wanted to thank all of you for your helpful responses/advice. It turns out that SAS has a transpose utility that accomplishes something similar to the code you gave that collapses many columns into one, so now I have a dataset with 3 columns and 500,000+ entries. It's definitely useful to have this type of functionality in access itself, so thanks to those who posted the code.