Averaging for non zero values or based on conditions

[khwaja]

I have a report that shows group averages for a set of record based on age of properties listed. Some of the properties are scheduled to be closed and I would like these properties ages to be excluded from being included in the group average. I would appreciate if someone could help me figure out the way to use average function with a condition at a group level. I have a field that shows 'CLosure' for such properties.

Cheers

 

[Bastien]

One of the easiest ways is to use SQL to query the table fo only the values that you want. You can use the math functions in SQL to get the AVG easily and just show the result in whatever field that you want.

Sample code for a table query:
Dim strSQL As String
Dim dbs As Database
Dim rstemp As Recordset


'set the value of home or work adrress to mail to
If Me.MemberAddressHome.Value = 1 Then
strAddrType = "S"
ElseIf Me.MemberAddressHome.Value = 2 Then
strAddrType = "H"
Else
MsgBox "Please check either Home or Work Address for mailing"
Me.frmeChooseAddress.SetFocus
End If

'build the SQL query for either SIN or EID
strSQL = "SELECT DC_DBA_CME_PERSON.FIRST_NAME, DC_DBA_CME_PERSON.LAST_NAME, DC_DBA_CME_PERSON.SSN,"
strSQL = strSQL & "DC_DBA_CME_PERSON_ADDR.ADDR_LINE1, DC_DBA_CME_PERSON_ADDR.ADDR_LINE2,"
strSQL = strSQL & "DC_DBA_CME_PERSON_ADDR.ADDR_LINE3 , DC_DBA_CME_PERSON_ADDR.ADDR_LINE4, DC_DBA_CME_PERSON_ADDR.CITY, "
strSQL = strSQL & "DC_DBA_CME_PERSON_ADDR.STATE_CD, DC_DBA_CME_PERSON_ADDR.POSTAL_CD, DC_DBA_CME_PERSON_ADDR.COUNTRY_CD,DC_DBA_CME_ASSIGNED_PID.BASE_ID FROM "
strSQL = strSQL & " (DC_DBA_CME_ASSIGNED_PID INNER JOIN DC_DBA_CME_PERSON ON DC_DBA_CME_ASSIGNED_PID.PERSON_ID = DC_DBA_CME_PERSON.PERSON_ID) "
strSQL = strSQL & " INNER JOIN DC_DBA_CME_PERSON_ADDR ON DC_DBA_CME_PERSON.PERSON_ID = DC_DBA_CME_PERSON_ADDR.PERSON_ID WHERE "

'check for data to be filled in
If (Nz(Me.EmployeeNo.Value)) = "" And (Nz(Me.SIN.Value)) = "" Then
MsgBox "Please enter an Employee ID or a SIN into the approriate field"
DoCmd.CancelEvent
ElseIf Me.EmployeeNo.Value <> &quot;&quot; And (Nz(Me.SIN.Value)) = &quot;&quot; Then
strSQL = strSQL & &quot; (DC_DBA_CME_ASSIGNED_PID.BASE_ID) Like '&quot; & Me.EmployeeNo.Value & &quot;'&quot;
ElseIf Me.SIN.Value <> &quot;&quot; And (Nz(Me.EmployeeNo.Value)) = &quot;&quot; Then
strSQL = strSQL & &quot; (DC_DBA_CME_PERSON.SSN) Like '&quot; & Me.SIN.Value & &quot;'&quot;
ElseIf Me.EmployeeNo.Value <> &quot;&quot; And Me.SIN.Value <> &quot;&quot; Then
strSQL = strSQL & &quot; (DC_DBA_CME_PERSON.SSN) Like '&quot; & Me.SIN.Value & &quot;'&quot;
End If

'set the address type for the query
strSQL = strSQL & &quot; and (DC_DBA_CME_PERSON_ADDR.ADDR_TYPE) Like '&quot; & strAddrType & &quot;'&quot;

'MsgBox strSQL

Set dbs = CurrentDb()

'run the query
Set rstemp = dbs.OpenRecordset(strSQL)

'assign the values
Dim strName As String
Dim strAddress As String
Dim strSIN As String

'check to see if there is a record returned and cancel the event or fill in the values


If rstemp.RecordCount = 0 Then
MsgBox &quot;No address found for this person. Change address type&quot;
rstemp.Close
Set rstemp = Nothing
Set dbs = Nothing

If Me.EmployeeNo.Value <> &quot;&quot; Then
Me.EmployeeNo.SetFocus
Else
Me.SIN.SetFocus
End If

Else

strName = rstemp(1).Value & &quot;, &quot; & rstemp(0).Value
If rstemp(3).Value <> &quot; &quot; Then
strAddress = rstemp(3).Value & vbNewLine
End If
If rstemp(4).Value <> &quot; &quot; Then
strAddress = strAddress & rstemp(4).Value
End If
If rstemp(5).Value <> &quot; &quot; Then
strAddress = strAddress & &quot;, &quot; & rstemp(5).Value & vbNewLine
End If
If rstemp(6).Value <> &quot; &quot; Then
strAddress = strAddress & rstemp(6).Value
End If
If rstemp(7).Value <> &quot; &quot; Then
strAddress = strAddress & &quot;, &quot; & rstemp(7).Value & vbNewLine
End If
strAddress = strAddress & rstemp(8).Value & vbNewLine
strAddress = strAddress & rstemp(10).Value & vbNewLine
strAddress = strAddress & rstemp(9).Value

strSIN = rstemp(2).Value
Me.EmployeeNo.Value = rstemp(11).Value
Me.EmployeeName.Value = strName
Me.MemberAddress.Value = strAddress
Me.SIN.Value = strSIN
rstemp.Close
End If


Set rstemp = Nothing
Set dbs = Nothing

hth
Bastien

There are many ways to skin this cat,
but it still tastes like chicken

 

[khwaja]

Bastien

Much appreciate your help. I am sure I can use this code in a bigger context but at this stage I have a very simple requirement which should probably be met using an expression. What I need is to put a conditional expression in a footer field at group level which is basically averaging ages of stores. There are certain stores which are up for closure and I would like them to be excluded from being part of averaging. In Excel, it can be done relatively easily. But in this case, I am unable to find an averaging function that can be conditional at group level.

Thanks

 

[khwaja]

Just briging it to the fore for the attention of members. If someone could kindly help?

Cheers

 

[WildHare]

Check out the DAVG domain aggregate function. It should do what you want, esp. if you can 'select out' the stores marked for closure.

Something like

Davg(&quot;[AGE]&quot;, &quot;[tblStores]&quot;, &quot;[tblStores]![ToBeClosed] = FALSE&quot

would appear to get you most of the way there, I think... Remember, you're unique - - just like everyone else
Another free Access forum:

http://www.forumco.com/jmhare

http://www.jmhare.com