Extract data from query (1 Viewer)

[gbil]

Hello.

i am trying to extract data from a query but access help is confusing (sorry, my fault).

part of code when form loads, my questions is bold:

'query criteria
vOrgCode = Me.tOrgCode

'sql statement
strSQL = "Select tblMembers.tOrgCode, tblMembers.tGender, tblMembers.nMonthlyIncome, "
strSQL = strSQL & "tblMembers.nFarmSize, tblMembers.tPrimeCrop, tblMembers.nNoHills "
strSQL = strSQL & "From tblMembers Where tOrgCode = '" & vOrgCode & "'"

Set cn = CurrentDb 'the database in this form
Set rs = cn.OpenRecordset(strSQL, dbOpenSnapshot) 'snapshot to avoid disaster like delete

vnTotMemb = expression 'how do i count number of records? in excel this is CountA()
vnTotMale = expression 'how do i count male or female from record? in excel CountIf(), i believe
vnAveIncome = expression 'Average,how do i do this? in excel this is Average()
vnHighIncome = expression 'high or min in excel
vnTotFarmSize = expression 'sum()?

i cannot build a simple query and save it as reference since the number of organizations may be 25 at the minimum. this is why i want to build it on the fly, so to speak.

 

[namliam]

All these things are possible to do utilizing a "Group by" query...
You can actually list it per Organization and Sum/Count/Avg/etc what ever you want/need....

Research it for a bit and see where you get, feel free to come back with detailed questions.

 

[gbil]

ok.

thank u for your attention.

i guess i have to do it a long way until i found something to make it more dynamic.

 

[gbil]

got it!

Set db = CurrentDb
'strSQL = "Select (COUNT(qryMembers.tGender))"
'strSQL = strSQL & " AS [MaleCount]"
'strSQL = strSQL & " FROM qryMembers "
'strSQL = strSQL * " WHERE qryMembers.[tGender] = 'Male' AND qryMembers.[tOrgCode] = '" & strVariable & " ' ;"
'the above gives me data type mismatch error, i don't know why //here is the error, edited with red

'below is running good
strSQL = "SELECT (COUNT(qryMembers.tGender))AS [MaleCount]FROM qryMembers WHERE qryMembers.[tGender] = 'Male' AND qryMembers.[tOrgCode] = '" & strVariable & " ' ;"
Set rst = db.OpenRecordset(strSQL)
rst.MoveFirst

Me.txtTotMale = rst.Fields("MaleCount")