Percentage Of Males And Females

[Ginty]

how would i work out the percentage of males and females in my database?

i have done three queries.. one to count everyone, one to count males and one to count females.

is there an expression that could calculate this?
something like [Query:Count Males]/[QUERY:TOTALCOUNT]*100

 

[KenHigg]

How / where do you need it? In a report or maybe in a text box on a form?

 

[Ginty]

I want it for a graph so the data would be fine anywhere, in another field row within a query, or in a report.

as long as it automatically updates when data is changed...

 

[KenHigg]

As long as the total number of records was not really large, I would use some dcount()'s. Have you used dcount before?

 

[Ginty]

No i haven't used that function before.

i dont think the it would go over 750.

 

[KenHigg]

Tinker with it and I think it'll work pretty good for what you need. VB help cover it pretty good... Also, dcount() may work better

Something like:

Dcount("[myFldName]", "myTableName", "[myFldName] = 'male'")

etc...
etc...

 

[Ginty]

so


int males = Dcount("[SEX]", "TBDETAILS", "[SEX] = 'M' ")

would work out the number of males.

Dcount("[SEX]", "TBDETAILS")

this would calculate everyone.

how would i work out the percentage from here?

would this work? int percentageofmales = males / total * 100

 

[KenHigg]

Sure...

Or simply:

= Dcount("[SEX]", "TBDETAILS", "[SEX] = 'M' ") / Dcount("[SEX]", "TBDETAILS") * 100

 

[Ginty]

yeah i could use that.

i have coded this,

Public Function getPercentageOfMales()

Dim males As Integer

Dim total As Integer

Dim per As Integer


males = DCount("[SEX]", "ALL DETAILS", "[SEX] = 'M'")

total = DCount("[SEX]", "ALL DETAILS")

per = males / total * 100


getPercentageOfMales = per
Exit Function

End Function


When i call the function in a query it returns the value 600 times(same amount as the people in the database).

Would i be able to create a report and call the function in there?

 

[KenHigg]

When i call the function in a query it returns the value 600 times(same amount as the people in the database).

Which is exactly what it should do...

Would i be able to create a report and call the function in there?

Sure!

 

[Ginty]

your right it is.

i have linked the function to a report now, thanks a lot for all your help.

 

[KenHigg]

You may want to stay away from 'total' as a var name

 

[Ginty]

You may want to stay away from 'total' as a var name

noted. thanks again