Calculating percentages and other stats (1 Viewer)

[stu_gnu]

Hi

Is it possible in Access to create functions which can calculate percentages and other statistical information based on the data contained within a database.

 

[KeaganQuilty]

Absolutely. Give me an example of what you need and I'll help you out as best I can.

Keagan Quilty
Kilbride, Newfoundland

 

[stu_gnu]

Hi Keagan

sorry for not getting back to you sooner. However with regards to my problem with percentages want I want to do is find out the percentage of people in my survey who have answered yes or no to whether they have bought a certain product or not. I currently have 200 records in my table and this is likley to rise to around 250 - 270. Any ideas how I can work out quickly the percentage of people how have answered yes, and the percentage who have answered no?

many thanks
Stu

 

[Pat Hartman]

Here's a sample query that calculates a percent:

SELECT Count(*) AS CountYes, DCount("Required","tblWHtableFields") AS TotalRows, CountYes/TotalRows AS PercentYes
FROM tblWHTableFields AS F
GROUP BY F.Required
HAVING (((F.Required)=True));

 

[stu_gnu]

Not to sure how to work this formula Pat, could you give me a few more hints please?

thanks

Stu

 

[AlanS]

Here's another approach, that you can simply insert into any standard module, and call from anywhere in the database. The first argument to the DCount function identifies the field you are counting, the second identifies the table, and the third (optional) states a criteria for limiting the records included in the count - in this case it is simply the name of a Boolean field.

Public Function PercentWhoBought () as Single
Dim Overall as Integer, Bought as Integer
Overall = DCount("bBoughtWidget", "tblResults")
Bought = DCount("bBoughtWidget", "tblResults", "bBoughtWidget")
If Overall > 0 Then
PercentWhoBought = Bought / Overall
Else
PercentWhoBought = 0
End If
End Function

[This message has been edited by AlanS (edited 08-14-2001).]

[This message has been edited by AlanS (edited 08-14-2001).]

 

[Pat Hartman]

Simply replace YourColumnName and YourTableName with the appropriate names.

SELECT Count(*) AS CountYes, DCount("YourColumnName","YourTableName") AS TotalRows, CountYes/TotalRows AS PercentYes
FROM YourTableName AS F
GROUP BY F.YourColumnName
HAVING (((F.YourColumnNam)=True));