Need help to get perrcentages

[BaconMakinWabbit]

HEllo all. I appreciate any help you may be able to provide in this matter.
I have an Access table that someone made to capture the results of a survey. They used a lot of yes/no fields and I need to capture all percentages on one report. I am not sure of Access has this functionality built in or if I need to write some VBA to handle the %'s.
I wrote this function that I was going to use to calculate the percentage of Yes answers:

Public Function Percent(ColumnName As String) As Variant

Dim TotalRec As Long
Dim YesRec As Long
Dim dbs As Database
Set dbs = CurrentDb
Dim record1 As Recordset
Dim record2 As Recordset
Dim SQLColumn As Variant

SQLColumn = ColumnName
Set record1 = dbs.OpenRecordset("SELECT COUNT(" & SQLColumn & ") as COUNT1 FROM Survey_Fact WHERE " & SQLColumn & " = 'Yes';")
Set record2 = dbs.OpenRecordset("SELECT COUNT(" & SQLColumn & ") as COUNT2 FROM Survey_Fact;")

If (record1.RecordCount = 0 Or record2.RecordCount = 0) Then
Set record1 = Nothing
Set record2 = Nothing
MsgBox "This item does not exist", vbExclamation + vbOKOnly, "Error"

Else

YesRec = Val(record1!COUNT1)
TotalRec = Val(record2!COUNT2)

End If
Percent = (YesRec / TotalRec) * 100
End Function

I am new at this so I have a couple questions:

1) Is this even necessary or is there a way I can have one report with the format of...
Answer 1) 45% of people said yes, Answer 2) 17% of people said yes...so on and so forth, without adding a pile of subreports with dozens of little dinky queries.

2) If I DO have to create a function, what am I doing incorrectly that when I use this in a select I get a row returned for each row int he database. This function should return a single value for each column.

I know these are dumb questions but this is my first Access VBA project. Used to mySQL, php, Java and such.

Thanks

 

[Blorf]

Can you post a few rows of data? You should be able to summarize with a fairly simple query.

ChaZ

"When religion and politics ride in the same cart...the whirlwind follows."
Frank Herbert

 

[Zion7]

BakinMakinWabbit, would DCount work?

Public Function Percent(ColumnName As String) As Variant

Dim iYes, iTotal As Integer

iYes = DCount(ColumnName, "Survey_Fact", ColumnName & " = True")
iTotal = DCount(ColumnName, "Survey_Fact")

Percent = (iYes / iTotal) * 100

End Function

You can try this, in the immediate window, to check results...
?Percent("YourColumnName")

Hope this helps, good luck!