I need to create a custom aggregate function (1 Viewer)

Harry74 · Jan 16, 2008

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

pbaldy · Jan 16, 2008

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.

Harry74 · Jan 16, 2008

pbaldy said:
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?

pbaldy · Jan 16, 2008

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

Code:

  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

I need to create a custom aggregate function (1 Viewer)

Harry74

New member

pbaldy

Wino Moderator

Harry74

New member

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread