Steven,
The problem with the mode is that it can return multiple values (bi-modal),
so what do you want to return if there are mutiple values which are "most
common" for a particular household? I have a function on my home computer
(not there right now) that I use for this (assumes a single mode). It looks
something like (untested):
Public Function fnMode(FieldName as String,
PKFieldName as String, _
Source as String, _
Optional Criteria as Variant = Null) as
Variant
'Returns a variant because the 'FieldName' field could be any data type.
Dim strSQL as string
Dim rs as DAO.Recordset
Dim lngFreq as Long
strSQL = "SELECT [" & FieldName & "], " _
& "COUNT([" & PKFieldName & "]) as Freq " _
& "FROM [" & TableName & "] " _
& ("WHERE " + Criteria) _
& " GROUP BY [" & FieldName & "] " _
& " ORDER BY COUNT([" & PKFieldName & "]) DESC"
strSQL = Replace(strSQL, "[[", "[")
strSQL = Replace(strSQL, "]]", "]")
Set rs = Currentdb.Openrecordset(strsql,,dbfailonerror)
if rs.eof then
fnMode = NULL
else
fnMode = rs(0)
endif
rs.close
set rs = nothing
End Function
Then, to use this in a query, you would do something like the following
(assumes you have a PK field named ID):
SELECT T.[nhouseholdID], fnMode("[DonationAmount]", "ID", "yourTable",
"[nHouseholdID] = " & t.nhouseholdID) as Mode
FROM yourTable
GROUP BY T.nHouseHoldID
