hello I need to create an aggregate function that will give me the product of my results

for example
I have a table with the following info
State City Data
PA Philly .9
PA St College .8

I want to create a query grouped on the state that will give the product of .8 and .9

end result should be
State Product of Data
PA .72

I know how to create a function but how can I make it an aggregate function (like the SUM function

Thanks alot

The only way that comes to mind would be creating a function that takes the state as a parameter. Within the function, open a recordset that gets all the records for the chosen state. Set the return value to 1. Within a loop of those records, multiply the return value times the data from the current record. It should end up with the product of all the records.

The only way that comes to mind would be creating a function that takes the state as a parameter. Within the function, open a recordset that gets all the records for the chosen state. Set the return value to 1. Within a loop of those records, multiply the return value times the data from the current record. It should end up with the product of all the records.

thanks
I have a loop in my function but how do i open a record set?

From my code library, opening a recordset with a loop:

Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()

strSQL = "SELECT..."
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

Do While Not rs.EOF
'do your thing
rs.MoveNext
Loop

set rs = nothing
set db = nothing