By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,807 Members | 1,492 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,807 IT Pros & Developers. It's quick & easy.

Help with average and percentage calculation from a query

P: 30
I am using Access 2003. I am setting up a form where a user selects a Clinic or Ward, beginning date, and ending date, and the form outputs the averages of a few fields based on that criteria. I have come up with this so far for the query -

Dim strSQL As String
strSQL = ""
If chkClinic.Value = -1 Then
strSQL = "Select avg(Courtesy), avg(Timeliness), avg(Confidence), avg(Privacy), " & _
"avg(Sensitivity), avg(Cleanliness), avg(Advice), avg(Overall) FROM tblScores " & _
"WHERE Clinic = '" & Me!cboClinic.Value & "' AND Date >= " & Me!txtBegin.Value & _
" AND Date <+ " & Me!txtEnd.Value
Else
strSQL = "Select avg(Courtesy), avg(Timeliness), avg(Confidence), avg(Privacy), " & _
"avg(Sensitivity), avg(Cleanliness), avg(Advice), avg(Overall) FROM tblScores " & _
"WHERE Ward = '" & Me!cboWard.Value & "' AND Date >= " & Me!txtBegin.Value & _
" AND Date <+ " & Me!txtEnd.Value
End If

But I'm having trouble getting the data pulled from that query into the proper text boxes (txtCourtesy, txtTimeliness, etc.). I've been pulling it into a recordset but I can't seem to get anything to work right. I think I just don't really have a grasp on how to manipulate a recordset yet.

Also - there is another category, "Handwashing," in addition to these others. Whereas the other factors are on a 0-5 scale, so an average works fine, Handwashing is boolean. I would like to calculate Handwashing so that the query returns a percentage of numerator "true" and denominator of total records. I imagine I'll be able to do this part pretty easily once I figure out the other part.
Sep 17 '07 #1
Share this Question
Share on Google+
1 Reply


FishVal
Expert 2.5K+
P: 2,653
Hi, there.

When you run SELECT query with calculated fields. The latter are being returned with default field names - like ExprXXXX. Do you refer them by this names?
If you want to explicitely set the name of calculated field, then use "AS" keyword.
e.g.
Expand|Select|Wrap|Line Numbers
  1. SELECT Avg([FieldName]) AS AvgFieldName FROM [tblTable];
  2.  
Sep 17 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.